calc age in years between two dates

[jodibee]

Hi,

Need help with an inconsistent calculation in Filemaker 5.5. I need to calculate age based upon the year difference between two dates. The calculation created before my time was

Case(not IsEmpty(Date of Death) and not IsEmpty(Date of Birth), (Round((Date of Death - Date of Birth)/365.25,1)), "")

This sometimes works, sometimes doesn't. Can someone please help me out with a nice, neat reproducable calculation?

Thanks

[Vaughan]

Age, in number of years, number of days, or what?

[jodibee]

I ended up using

NumToText(Year(date_diagnosis) - Year(dob) - Case(date_diagnosis< Date(Month(dob), Day(dob), Year(date_diagnosis)), 1, 0))

This works fine if the date field is formatted correctly. The underlying problem that I have found, which has created the inconsistency, is that the "date" fields were originally defined as a text field. I converted them to date fields and the dates display properly, however, there is obviously some underlying problem in the date formatting that makes the calculation result come out as year of the date of birth instead of calculating the difference in years between the two dates. Do you have any ideas about how to change the date field definitions/formats to resolve this problem without having to manually re-enter all the dates?

[Oldfogey]

Are you sure you converted the dates correctly from text? I've done this and it's a nightmare. Have you done a find for invalid dates?

Are you still checking foe empty dates round your new calc? Are both dates ALWAYS there?

What do you mean re your original calc "..sometimes works, sometimes doesn't"?

Are you using 4-digit years?

[jodibee]

Yes I am using four digit year dates, but it is more than possible, probably more probably than not that the dates were not converted correctly. The majority of them are invalid dates after completing the recommended find. The sometimes works thing implied that the formula works with valid dates but not with invalid dates, this I did not know until I realised that it was a problem with date validity.

How do I go about converting them correctly?

[Oldfogey]

Find all your invalid dates and go back to the text for those records. Possible?

You then need to analyse them to group various formats, eg dd/mm/yy, d/m/yyyy, etc

Any month/day names used? Having worked out the different formats, standardise them to one format. (I won't make a suggestion - it depends on what you've got.)

I love giving that sort of advice because it covers a hell of a lot of work. You virtually need to do it manually - sort the records and play around with Replace.

You can then extract the individual bits and use them directly in a calc or put them in separate fields.

NewDate = Date(TextotNum(month_bit), TexttoNum(day_bit), TexttoNum(year_bit))

Let us know what kinds of formats your invalid dates are in and I/we will give you more specific advice.

[Vaughan]

"I love giving that sort of advice because it covers a hell of a lot of work."

Only if you're charging by the hour.