Trying to do an Age calculation...

[eric.heggie]

The data I am working with is formatted as yyyymmmdd

I assume I need to use the currentdate function, however when I ask for current date it gives some number that is not formatted same way. I would like this to work so I don't have to change it again.

Thanks

[-Queue-]

Is it a text or date field? If it is a date field, create a calculation field with:

Date( Month(x), Day(x), Year(x) )

where x is the field name.

If it is a text field, try a calc field with:

Date( Middle( x, 5, 2 ), Right( x, 2 ), Left( x, 4 ) )

Either will produce a date result that you can then use with other date functions such as the current date one.

[eric.heggie]

can you explain what each of those middle etc... is doing?

[eric.heggie]

also right now it is a date field, but I imported it from an access database where it was either a number or text.

[Mountainoak]

Perhaps you'll have some use for this. i`m using it myself and it gives the age in years/months/days. Maybe this age is to detailed for you but look at the code and change it to your needs. By the way there are plenty of these code on the internet (copy paste).

AGE.fp7.zip

[-Queue-]

They are merely text functions to extract the leftmost (for the year), rightmost (for the day), or middle (for the month) characters to put into the Date function.

Are you saying that it is a date field with data entered as yyyymmdd or that it is merely formatted that way on the layout?

[eric.heggie]

it is a date field with data entered yyyymmdd

[-Queue-]

The data is entered as yyyymmdd into FileMaker? I didn't even know that was possible.

Well, in that case, I may have given you unnecessary information. Perhaps if you explicitly state what your goal is and what you are working with, we'll be able to help more.

[comment]

I suspect the data was imported as text into a date field, where it is now stored as invalid dates. To make sure, I would search the field with a question mark.

[LaRetta]

I didn't even know that was possible.

I believe if it is an import, FM can't stop it but it will come up as invalid if searched for ? and if modified, FM will squawk.

[-Queue-]

Ah, what if one changed the field type to text and created a date field with the above calculation? Seems like it would resolve the importing issue.

[LaRetta]

Yes. One can't apply Set Field [] directly against the date field. I believe another field would need to be used to correct it.

God, it's good to have you back. :wink2:

Edited March 20, 2008 by Guest

[comment]

One can't apply Set Field [] directly against the date field

Why not? It may be preferable to do it the way JT suggested (since it's reversible), but it's not a must.

[-Queue-]

I would think reading invalid date data would cause a problem.

[comment]

I don't think so. The data is invalid when an attempt is made to interpret it as a date. Otherwise it's just data.

[-Queue-]

Good call! I just tested and you are correct, sir.

[eric.heggie]

so I need to basically convert it to text and then create a new date field?

[-Queue-]

No, once you have imported a new set of records, just run a Set Field loop to convert the date value appropriately. Since I don't know the specific script steps in FM 8, I'll use pseudo-code.

Go to Record [First]

Loop

Set Field [datefield; Date( Middle( datefield, 5, 2 ), Right( datefield, 2 ), Left( datefield, 4 ) )]

Go to Record [Next; Exit after last]

End Loop

For the existing records, run a Replace Field Contents in the date field with the calculation above.

This will convert all the invalid dates into a valid date format.