Subtracting dates from each other

[AutismLab]

Hi!

Here's my problem:

I need to subtract a person's birthdate from another date in order to determie how old that person is in Years, Months and Days.

Example

Today:--------------- 2005-06-15 (YYYY-MM-DD)

Child's Birthday:-----2003-11-14 (YYYY-MM-DD)

Answer should be:----- 1-07-01 (1 year, 7 months and 1 day old.)

HOWEVER, I keep getting this answer from the existing code:

19 Months 9 Days (translates into 1 year, 7 months, 9 days old)

Wrong Answer...

Here's another example of the weird dates it is outputing:

Example

Today:--------------- 2005-06-15 (YYYY-MM-DD)

Child's Birthday:-----2003-11-07 (YYYY-MM-DD)

Answer should be:----- 1-08-08 (1 year, 8 months and 8 days old.)

HOWEVER I am getting 19 months and 16 days (1 year, 7 months, and 16 days)

The current code in the calculation is as follows:(written by some Professer in another University) ~~~~>

Truncate((Get(CurrentDate)-C_DOB)/30;0) & " Months " & Mod(Get(CurrentDate)-C_DOB;30)& " Days"

I am sure there is a better way to do this. Why am I getting weird dates as an output?

The current format is to give me just the months and days as an output

(ex 2 years and 6 months = 30 months)

Format does not HAVE to be output as MM/DD. Years, months and days are fine as well. I just need them to be accurate and constant. I've been working on this for a few hours and can not make it do what I want.

Any suggestions or help?

THANKS!

[stanley]

Autismlab:

The reason you're getting the strange result is that your calculation is arbitrarily assigning a month-length of 30 days, whereas only 4 months have 30 days, 1 has 28 or 29 days, and the other 7 have 31. To begin with, if you just subtract your dates from each other, you'll get a number (which is the number of days (total) between the two dates. Converting that into years, months and days will require a fairly complex nested Case() statement which takes into account the lengths of the months and leap years. Perhaps someone has already created this calculation, and will be kind enough to shoot it your way; otherwise, you may want to search through the Forums and see if it's already out there.

-Stanley

[-Queue-]

I have not thoroughly tested this yet, but you could try

Let([

T = Get(CurrentDate);

Yt = Year(T);

Yd = Year(DOB);

Md = Month(DOB);

Mt = Month(T);

Dd = Day(DOB);

Dt = Day(T);

Y = Yt - Yd - (T < Date( Md; Dd; Yt ));

M = Mod( Mt - Md + 12 - (Dt < Dd); 12 );

D = Dt - Dd + Case( Dt < Dd; Case( Day(T - Dt) < Dd; Dd; Day(T - Dt) ) )

];

Case( Y; Y & " year" & Case( Y > 1; "s" ) ) &

Case( M; Case( Y; ", " ) & M & " month" & Case( M > 1; "s" ) ) &

Case( D; Case( Y + M; ", " ) & D & " day" & Case( D > 1; "s" ) )

)

[QuinTech]

Hello, and welcome to the Forums!

The discrepancy you see exists because your calculation implicitly assumes all months are 30 days long.

Truncate((Get(CurrentDate)-C_DOB)/[color:"red"]30;0) & " Months " & Mod(Get(CurrentDate)-C_DOB;[color:"red"]30)& " Days"

Try this on for size:

Case( 

  Year( Get( CurrentDate) ) < Year( C_DOB ) or ( Year( Get( CurrentDate) ) = Year( C_DOB ) and Month( Get( CurrentDate) ) < Month( C_DOB ) ) or ( Year( Get( CurrentDate) ) = Year( C_DOB ) and Month( Get( CurrentDate) ) = Month( C_DOB )  and Day( Get( CurrentDate) ) < Day( C_DOB ) ) ;

   "#N/A" ; 













Int( ( ( Year( Get( CurrentDate) ) * 12 + Month( Get( CurrentDate) ) ) - ( Year( C_DOB ) * 12 + Month( C_DOB ) ) ) / 12 ) & 

" Years " & 

Mod( ( Year( Get( CurrentDate) ) * 12 + Month( Get( CurrentDate) ) ) - ( Year( C_DOB ) * 12 + Month( C_DOB ) ) ; 12 ) & 

" Months " & 

Case( 

  Day( Get( CurrentDate) ) >= Day( C_DOB ) ; 

   Day( Get( CurrentDate) ) - Day( C_DOB ) ; 

   ( Day( Get( CurrentDate) ) + 28 + Middle( "303232332323" ; Month( Get( CurrentDate) ) - 1 ; 1 ) ) - Day( C_DOB )  

) & " Days" 















)

I've only done limited testing on this, but I think it should work.

Jerry [color:"red"]

[QuinTech]

[AutismLab]

THANK YOU JERRY! THANK YOU QUEUE! (and Stanley too)

I REALLY appreciate it.

I have never worked with FileMaker before, and was given the responsibility of fixing all the bugs in this database. (Why God? Why did they have to know I used to be an I.T geek? Now they think I can fix everything in this lab!)

So, if you could, a little explaining of what the heck is going on in that code would be helpful. I understand most of the logic, but I don't know how FileMaker works.

also, Jerry, can you please explain this line, cause you lost me!:

( Day( Get( CurrentDate) ) + 28 + Middle( "303232332323" ; Month( Get( CurrentDate) ) - 1 ; 1 ) ) - Day( C_DOB ) )

Sorry to be a bother- You guys are the best!

- Desirie

[QuinTech]

Yeah, arcane, I know.

Starting with:

Middle( "303232332323" ; Month( Get( CurrentDate) ) ; 1 ) )

This returns 3 for January, 0 for February, 3 for March, ... if you watch the pattern and pretend that leap years don't exist, you will notice this corresponds to the offset from 28 for the number of days in each month.

Thus,

Middle( "303232332323" ; Month( Get( CurrentDate) ) [color:"red"]- 1 ; 1 ) ) gives the offset for the previous month,

[color:"red"]28 + Middle( "303232332323" ; Month( Get( CurrentDate) ) - 1 ; 1 ) ) gives the number of days in the previous month (again, not counting leap years),

[color:"red"]Day ( Get ( CurrentDate ) ) + 28 + Middle( "303232332323" ; Month( Get( CurrentDate) ) - 1 ; 1 ) ) gives the number of days since the first day of the previous month, and

[color:"red"]( Day ( Get ( CurrentDate ) ) + 28 + Middle( "303232332323" ; Month( Get( CurrentDate) ) - 1 ; 1 ) ) [color:"red"]- Day ( C_DOB ) ) gives the number of days between today and the day of the birthday.

Wish I could help you out on your new-found status as IT director for your lab. The only sure-fire strategy I've found to deal with that is to feign extreme stupidity. Maybe you can try to fix a hard disk with a paper clip. That should adjust their opinion of your abilities.