Month/ Year calculation (1 Viewer)

[mattkorguk]

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);

=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);

=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

 

[isladogs]

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]

@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]

You're welcome.
I have to confess I didn't even try to understand that long formula