Calculating ages with imperfect field data

[Scott_R]

Hello, all. I'm trying to create an "Age:" field calculated from two others. That is:

DATE-YOB=AGE

(YOB is "Year of Birth," not a full DOB)

Notes, requirements, & issues:

1) Because of my own inexperience, the DATE field is formatted as a number rather than as a date field.

2) I need to deal only in years, not full dates (approximate ages). So we're talking basically

AGE = 2006-1978

and AGE simply containing "28"

3) YOB is formatted as a number, and it contains only a year, not date-month. So YOB is always

1979

1987

and so on

DATE is formatted as

dd-month-yyyy

with dd and yyyy as numbers, month as a word. I'll basically need to grab the last four digits of that field:

1986

from 12-September-1986

There are a handful of fields that don't have the date:

MONTH-YEAR

There are also plenty of fields where DATE and YOB are empty, or where one but not the other is filled.

The database is very highly populated, so any solution that would require field-by-field (manual) reformatting is a no-go.

[comment]

These two statements seem to contradict each other:

the DATE field is formatted as a number

DATE is formatted as

dd-month-yyyy

with dd and yyyy as numbers, month as a word.

[Scott_R]

These two statements seem to contradict each other:

the DATE field is formatted as a number

DATE is formatted as

dd-month-yyyy

with dd and yyyy as numbers, month as a word.

No contradiction, though perhaps I should have been more precise. The text is entered in date form, as described above, but the field Type is "Number," not "Date" (i.e., open "Manage Database..." and you would see that field listed as "Number" in the Type column).

Edited May 1, 2009 by Guest

[comment]

Then I'd suggest you change the field's type to Text, and try the following calculation (result is Number) =

Let (

end = Right ( DATE ; 4 )

;

Case ( end and YOB ; end - YOB )

)

[Scott_R]

That seems to have done the trick; thanks.

[Scott_R]

Actually, one further question:

in the above, if I sometimes had a full date in the YEAR OF BIRTH field, how could I calculate an accurate age?

That is, if everything else remained the same from the original question, BUT, a small percentage of records have YOB as

12-December-1980

so could therefore, in theory, do

Age=DATE-YOB

and get an accurate number (DATE is a specified date, not the current date) is there a way to calculate an accurate number from DATE while retaining YOB for the records where I don't have date-month, or would that require massive reformatting and/or a second AGE field for such calculations?

[comment]

What exactly do you mean by "an accurate number"? If you had two valid dates, subtracting them would return the number of elapsed days - not years. It is possible to make the calculation look at say, the number of words in YOB, and if there are three of them, convert both YOB and DATE from text to date, then calculate the accurate age in years. The difference between this and the previous calc will never be more than 1.

Is this a one-time thing, or do you intend to continue working with this data? And how many records are there?

[Scott_R]

What exactly do you mean by "an accurate number"? If you had two valid dates, subtracting them would return the number of elapsed days - not years. It is possible to make the calculation look at say, the number of words in YOB, and if there are three of them, convert both YOB and DATE from text to date, then calculate the accurate age in years. The difference between this and the previous calc will never be more than 1.

Is this a one-time thing, or do you intend to continue working with this data? And how many records are there?

"Accurate" meaning only that AGE ticks over to the next integer upon the anniversary of the birth. So, if

DOB: 05-June-1982

and

DATE: 02-February-2002

then

AGE: 19

but if

DOB: 05-June-1982

and

DATE: 02-July-2002

then

AGE: 20

-- IOW, how a person normally states their age, rounding down from any count of days less than 365

Summary of some of the complications:

1) either the YEAR OF BIRTH or the DATE fields could be empty

2) YEAR OF BIRTH might only contain a four-digit year or, in a minority of cases, a DD-MMMM-YYYY (as 12-July-2005, for example); DATE, if filled, is almost always DD-MMMM-YYYY, but could be MMMM-YYYY)

Really, the most important objective is to able to have the full date in YOB (to make it DOB instead), rather than to calculate the accurate AGE. That is, I'd like to be able to do that calculation, but I'm more interested in having a detailed DOB field without screwing up the AGE field (even if AGE ends up off by 1).

It's ongoing, and it's a BIG database--as in the tens of thousands.

[comment]

the most important objective is to able to have the full date in YOB (to make it DOB instead)

That would be useful - but since you don't have enough information for a full date, you'd need to make an arbitrary assumption regarding the missing month/day (Filemaker doesn't recognize partial dates).

Or perhaps you'd prefer to split the YOB into 3 separate (number) fields, and fill in the blanks only when and as you need them (this would allow you to make different assumptions for different needs).

I don't know what the nature of your data is, and what do you intend to do with it - so it's difficult for me to advise which would be better. It's a business decision, in any case.

[Scott_R]

ld be useful - but since you don't have enough information for a full date, you'd need to make an arbitrary assumption regarding the missing month/day (Filemaker doesn't recognize partial dates).

Or perhaps you'd prefer to split the YOB into 3 separate (number) fields, and fill in the blanks only when and as you need them (this would allow you to make different assumptions for different needs).

I don't know what the nature of your data is, and what do you intend to do with it - so it's difficult for me to advise which would be better. It's a business decision, in any case.

So, either put in

00-XXXX-1978

00-XXXX-1983

etc. for all year fields or create three separate fields for DATE, MONTH and YEAR?

I'm not sure how to migrate the existing data over to a new form..

[comment]

Not exactly: "00-XXXX-1983" is not a valid date (day cannot be zero). It could be something like:

12-September-1986 -> 12/9/1986

September-1986 -> 1/9/1986

1986 -> 1/1/1986

or create three separate fields for DATE, MONTH and YEAR?

Yes.

I'm not sure how to migrate the existing data over to a new form..

I don't think you need to migrate the existing data. Just add a few fields to the existing table.

[Scott_R]

Yes.

I don't think you need to migrate the existing data. Just add a few fields to the existing table.

My concern is, if I have a

DATE

field that contains

12-July-2007

how do I move that data over to three separate fields,

DATE / MONTH / YEAR

especially when the existing

DATE

sometimes only contain

July-2007

?

A similar issue for the

YEAR (or DATE) OF BIRTH

field, which will mainly contain

year

but may also contain the full

date-month-year

I think I prefer using three separate fields rather than adding an arbitrary fake date to a single field, though I'd go with whatever works.

thanks for all your help.

[comment]

Try:

BirthYear =

RightWords (YOB ; 1 )

BirthDay = 

Case ( WordCount ( YOB ) = 3 ; LeftWords ( YOB ; 1 ) )

BirthMonth = 

Let ( [

mmm = Case ( 

WordCount ( YOB ) = 2 ; LeftWords ( YOB ; 1 ) ;

WordCount ( YOB ) = 3 ; MiddleWords ( YOB ; 2 ; 1 ) 

) ;

pos = Position ( "janfebmaraprmayjunjulaugsepoctnovdec" ; Left ( mmm ; 3 ) ; 1 ; 1 )

] ;

Case ( pos ; Ceiling ( pos / 3 ) )

)

All three are calculation fields with result type Number.

[Scott_R]

Whoops; for some reason I didn't get a notification of a reply.

OK, I puzzled it out and I have three fields, (dd, MM, YYYY) for birthdate. Since

AGE

cannot now be calculated from the

DOB (formerly YOB)

field, as it has become

dd-MMMM-YYYY

I've changed the

AGE

calcuation to use the new

BirthYear

field, for basically the same result.

How do I translate this all into the more-specific age calculation--i.e., ticking over to the next age at the birthdate?

E.g.,

AGE is 25

when

DOB is 02-February-1975

and

DATE

is at least 02-February-2000 but less than 02-February-2001

?

Here are the (relevant) fields I currently have:

------------

DATE

which contains a set date (not the current one) which is the upper number used to calculate

AGE

------------

DOB

which contains the three-part DOB (e.g.. 12-March-1979)

------------

Separate

DayOfBirth

MonthOfBirth

YearofBirth fields

which are auto-populated with their respective information from

DOB

------------

AGE

which is based on

YearofBirth

subtracted from the right-four characters of

DATE

------------

The data I have is:

DATE, which might be empty. If filled, it's DD-MMMM-YYYY (I'm in the process of changing occurrences of MMMM-YYYY to 01-MMMM-YYYY)

DOB might be empty; if filled, it's either YYYY or the full DD-MMMM-YYYY

And the aforementioned three new fields.

[comment]

I am not sure I follow this fully. It seems you have split the text in your "date" fields into two sets of three TEXT fields each. That's not going to help to solve the real issue here: Filemaker does not understand dates written as text (although it can DISPLAY valid dates as such).

If you had processed the "dates" into two sets of three NUMBER fields each, as described in my previous post, you could then construct a valid date using the Date function - for example:

Date ( BirthMonth ; BirthDay ; BirthYear )

or:

Case ( BirthYear ; Date ( Max ( 1 ; BirthMonth ) ; Max ( 1 ; BirthDay ) ; BirthYear ) )

(this last one arbitrarily substitutes 1 for missing month/day)

I'm in the process of changing occurrences of MMMM-YYYY to 01-MMMM-YYYY

I don't think that's necessary, or even desirable: you are depriving yourself of the possibility to make different assumptions for different calculations (for example, one could prefer to assume the month of July and the 15th day of the month, when these are unknown).