Jump to content

Date Calculation


This topic is 6502 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I, too, am fairly new to FileMaker, especially scripting. I have a FM database and have imported records from HyperCard, but some of the date fields have the wrong year. Instead of the year beign 2003 or 2004 it came in as 1903 and 1904. I'm trying to figure out a script that will change the year to the appropriate date, either 2003 or 2004.

At this point I'm pretty frustrated. Please help!

Link to comment
Share on other sites

Dave:

Here's a brute force method. I've called your date field "Your_Date_Field".

Write a script with the following steps:

Show All Records

Go To Record (First)

Loop

If "Year(Your_Date_Field"=1903

Insert Calculated Result Select,"Your_Date_Field", "Your_Date_Field+36525"

End If

If "Year(Your_Date_Field"=1904

Insert Calculated Result Select,"Your_Date_Field", "Your_Date_Field+36525"

End If

Go To Record (Next, Exit After Last)

End Loop

This just adds 36525 days to any date from 1903 or 1904 - that's a century with leap years included. If you've got more problem years, you could have a single If statement with something like "<1905" to catch all the old years.

It ain't elegant, but I hope that helps

-Stanley

Link to comment
Share on other sites

You can also do a replace script step such as:

go to layout [any layout with date_field on it]

Replace Contents[Date_Field,date(month(Date_Field),Day(Date_Field),if(year(Date_Field)<1950,year(Date_Field)+100,year(date_field))]

go to layout [original layout]

Jeff

Link to comment
Share on other sites

Stanley, I tried your script and it doesn't seem to work. I think the reason is that the imported data was in a dd/mm/yy format instead of the dd/mm/yyyy format required by FileMaker. Any idea how to change that?

Link to comment
Share on other sites

Dave:

If you set the date format to dd/mm/yyyy, then it comes out that you've got some that say 1903 or 1904, right? In which case it should work.

What kind of results are you getting from the script?

Actually, now that I look, I missed the closing parentheses on both Date() statements, in the lines that begin with "If" statements. The script should read:

Show All Records

Go To Record (First)

Loop

If "Year(Your_Date_Field")=1903

Insert Calculated Result Select,"Your_Date_Field", "Your_Date_Field+36525"

End If

If "Year(Your_Date_Field")=1904

Insert Calculated Result Select,"Your_Date_Field", "Your_Date_Field+36525"

End If

Go To Record (Next, Exit After Last)

End Loop

Now it should work.

-Stanley

Link to comment
Share on other sites

  • 1 month later...

Leb I Sol:

I think that Dave must have been using a fairly old HyperCard stack - I can't remember when Apple stopped developing HyperCard, but I'd imagine it was Y2K friendly (being an Apple product), but perhaps the stack just had two-digit years (or the dates were text? would that matter?) but nevertheless, he was coming in from HyperCard, and that's got to have something to do with it...

-Stanley

Link to comment
Share on other sites

This topic is 6502 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.