Year.Month calculation

[mattkorguk]

Hi all,
I'm working on something for a school that holds children's ages in year.month format. Following tests, their 'test' age is recorded in the same format. All they want then is a progress column which calculates the two fields, simple.
I'm currently using the following to get their 'age at test date';

=DATEDIF(B3,D3,"y") & "." & DATEDIF(B3,D3,"ym")

(Where B3 is their DoB and D3 is the test date)

If their DoB is 01/02/2007 the above shows 8.10 if the test date was 01/12/2015, seems ok.
When the teacher adds a test score age of 8.4, when you try and take one from the other, obviously Excel thinks it's decimal and returns 0.3. (-0.6 required)
Any suggestions much appreciated, thanks.

 

[mattkorguk]

OK, I went for a lookup table in the end, couldn't find another way around it. So using:

[FONT=Arial][SIZE=2]5.5[/SIZE][/FONT] [FONT=Arial][SIZE=2]1[/SIZE][/FONT]  
[FONT=Arial][SIZE=2]5.6[/SIZE][/FONT] [FONT=Arial][SIZE=2]2[/SIZE][/FONT]  
[FONT=Arial][SIZE=2]5.7[/SIZE][/FONT] [FONT=Arial][SIZE=2]3[/SIZE][/FONT]  
[FONT=Arial][SIZE=2]5.8[/SIZE][/FONT] [FONT=Arial][SIZE=2]4[/SIZE][/FONT]  
[FONT=Arial][SIZE=2]5.9[/SIZE][/FONT] [FONT=Arial][SIZE=2]5[/SIZE][/FONT]  
[FONT=Arial][SIZE=2]5.10[/SIZE][/FONT] [FONT=Arial][SIZE=2]6[/SIZE][/FONT]  
[FONT=Arial][SIZE=2]5.11[/SIZE][/FONT] [FONT=Arial][SIZE=2]7[/SIZE][/FONT]  
[FONT=Arial][SIZE=2]6.0[/SIZE][/FONT] [FONT=Arial][SIZE=2]8[/SIZE][/FONT]  
[FONT=Arial][SIZE=2]6.1[/SIZE][/FONT] [FONT=Arial][SIZE=2]9[/SIZE][/FONT]  
[FONT=Arial][SIZE=2]6.2[/SIZE][/FONT] [FONT=Arial][SIZE=2]10[/SIZE][/FONT]

Then 2 vlookups on the ages to get your calculation, so you might end up with 10-7, therefore I can return "3 months progress".

=IF(H3>12,"1 year /" & (H3-12) & " months", H3 & " months")

Still need a way to sort out the poor kids who show a negative result... working on that.