Month/ Year calculation (1 Viewer)

mattkorguk

Registered User
Joined
Jun 26, 2007
Messages
301
Hi all,
I need to run some 'age' calculations within an old version of Excel (2003) and the info I have is month and year (recorded age at time of test).
I might have 7.6 - child was 7 years and 6 months old. (I do have their DoB)
Following the test, their test age is recorded as 6.11 - 6 years and 11 months.
So, the answer I'm after is -0.6
So far I've tried (returns -1.4);
Code:
=F56-(DATEDIF(B56,D56,"y")& "."&DATEDIF(B56,D56,"ym"))
This doesn't seem to work as I'd hope.
I've also tried this to try and deal with the 10,11 and 12 issues (returns -1.5);
Code:
=IF(RIGHT(F56,LEN(F56)-FIND("/",F56))="10",(TRUNC(G56))-(TRUNC(D56))&"." &
(RIGHT(F56,LEN(F56)-FIND("/",F56)))-(RIGHT(C56,LEN(C56)-FIND("/",C56))),
IF(RIGHT(F56,LEN(F56)-FIND("/",F56))="11",(TRUNC(G56))-(TRUNC(D56))&"." &
(RIGHT(F56,LEN(F56)-FIND("/",F56)))-(RIGHT(C56,LEN(C56)-FIND("/",C56))),
IF(RIGHT(F56,LEN(F56)-FIND("/",F56))="12",(TRUNC(G56))-(TRUNC(D56))&"." &
(RIGHT(F56,LEN(F56)-FIND("/",F56)))-(RIGHT(C56,LEN(C56)-FIND("/",C56))),G56-D56)))
Any other suggestions would be great, thanks.
Matt
 
Last edited by a moderator:

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,365
I added code tags so its easier to read.
Can you explain why the answer should be -0.6 rather than -0.7

Suggest you do the calculation in months using a function
6.11=>83 and 7.6=>90 so the difference is -7
Then convert back by dividing by 12 for the years and using Mod 12 for the months
 

mattkorguk

Registered User
Joined
Jun 26, 2007
Messages
301
@Isladogs - That's it! I can't believe it was that straight forward. Thank you for the tidy up too!
I think I must have started down that route as I'd already created a lookup table with the Years.Month values, so I just added the month value and all sorted.

Thank you very much, I just need to update a few columns now.
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,365
You're welcome.
I have to confess I didn't even try to understand that long formula :cool:
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom