Calculating age differences

[Jo]

I have two columns: chronilogical age and reading age. I need to calculate
the difference between these ages. Age displayed as eg. 10:01 (10 years and
1 month) actual format is hh:mm.
Please can someone tell me the correct formula?
Thanks

 

[Stefi]

I'm not sure what chronological age and reading age are, but format hh:mm is
definitely NOT years and months but hours and minutes. Try to use format yy/mm
and formula =A1-A2 for the difference.
e.g. 10/01 - 08/5 will result 01/09.

Regards,
Stefi

„Jo†ezt írta:

 

[macropod]

Ah, but with =A1-A2 the displayed result isn't a meaningful age - it's simply an Excel interpretation of the date equal to
31/12/1899 plus the difference between A1 & A2 (I acknowledge that Excel inherited the 1900 leap-year bug from Lotus with respect to
the treatment of dates before 1 March 1900).

You might get a more meaningful result with:
=DATEDIF(A1,A2,"y")&"/"&MOD(DATEDIF(A1,A2,"m"),12)

Cheers

 

[Sandy Mann]

Using a time format to represent presents enormous, if not insurmountable
problems. I would suggest that you divide the years and months in two
cells. Thus the chronological age may be in C2& D2 as 12 and 7 respectively
meaning an age of 12 years and 7 months. The reading age may be in F2 & G2
as 11 & 6. The formula to work out the difference in ages is:

=DATEDIF(MIN(DATE(C2,D2,1),DATE(F2,G2,1)),MAX(DATE(C2,D2,1),DATE(F2,G2,1)),"y")&"
Years
"&DATEDIF(MIN(DATE(C2,D2,1),DATE(F2,G2,1)),MAX(DATE(C2,D2,1),DATE(F2,G2,1)),"ym")&"
Months"&IF(DATE(F2,G2,1)>DATE(C2,D2,1)," In Advance"," Behind")

If you want to prevent it showing 1Years 1 Months instead of 1 Year 1 Month
then use:

=DATEDIF(MIN(DATE(C2,D2,1),DATE(F2,G2,1)),MAX(DATE(C2,D2,1),DATE(F2,G2,1)),"y")&"
Year"&IF(DATEDIF(MIN(DATE(C2,D2,1),DATE(F2,G2,1)),MAX(DATE(C2,D2,1),DATE(F2,G2,1)),"y")<>1,"s
","
")&DATEDIF(MIN(DATE(C2,D2,1),DATE(F2,G2,1)),MAX(DATE(C2,D2,1),DATE(F2,G2,1)),"ym")&"
Month"&IF(DATEDIF(MIN(DATE(C2,D2,1),DATE(F2,G2,1)),MAX(DATE(C2,D2,1),DATE(F2,G2,1)),"ym")<>1,"s
"," ")&IF(DATE(F2,G2,1)>DATE(C2,D2,1)," In Advance"," Behind")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[David Biddulph]

Of course if the OP wants to input the data in time format such as 10:01 in
A2, then Excel can do the job of splitting it into your suggested separate
components, so the years in C2 would be =HOUR(A2), and the months in D2
would be =MINUTE(A2).

 

[Sandy Mann]

Of course if the OP wants to input the data in time format such as 10:01
in A2, then Excel can do the job of splitting it into your suggested
separate components, so the years in C2 would be =HOUR(A2), and the months
in D2 would be =MINUTE(A2).

Yes I tried that before I posted what I did but I thought that I was finding
errors with some times/Ageswhich I put down to the fact that with Ages/times
like 10:00 changing intoYears/Months because there is no month zero.
However, that said I find that it appears to be working this morning. The
forumula then becomes:

=DATEDIF(MIN(DATE(HOUR(C2),MINUTE(C2),1),DATE(HOUR(D2),MINUTE(D2),1)),MAX(DATE(HOUR(C2),MINUTE(C2),1),DATE(HOUR(D2),MINUTE(D2),1)),"y")&"
Year"&IF(DATEDIF(MIN(DATE(HOUR(C2),MINUTE(C2),1),DATE(HOUR(D2),MINUTE(D2),1)),MAX(DATE(HOUR(C2),MINUTE(C2),1),DATE(HOUR(D2),MINUTE(D2),1)),"y")<>1,"s
","
")&DATEDIF(MIN(DATE(HOUR(C2),MINUTE(C2),1),DATE(HOUR(D2),MINUTE(D2),1)),MAX(DATE(HOUR(C2),MINUTE(C2),1),DATE(HOUR(D2),MINUTE(D2),1)),"ym")&"
Month"&IF(DATEDIF(MIN(DATE(HOUR(C2),MINUTE(C2),1),DATE(HOUR(D2),MINUTE(D2),1)),MAX(DATE(HOUR(C2),MINUTE(C2),1),DATE(HOUR(D2),MINUTE(D2),1)),"ym")<>1,"s
"," ")&IF(DATE(HOUR(D2),MINUTE(D2),1)>DATE(HOUR(C2),MINUTE(C2),1)," In
Advance"," Behind")

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Actually now that mu brian has started working again, I recall that the
problem I had was returning the age difference in time format but even that
seems to be working this morning:

=TEXT(TIME(DATEDIF(MIN(DATE(HOUR(C2),MINUTE(C2),1),DATE(HOUR(D2),MINUTE(D2),1)),MAX(DATE(HOUR(C2),MINUTE(C2),1),DATE(HOUR(D2),MINUTE(D2),1)),"y"),DATEDIF(MIN(DATE(HOUR(C2),MINUTE(C2),1),DATE(HOUR(D2),MINUTE(D2),1)),MAX(DATE(HOUR(C2),MINUTE(C2),1),DATE(HOUR(D2),MINUTE(D2),1)),"ym"),0),"hh:mm")&IF(DATE(HOUR(C2),MINUTE(C2),1)<DATE(HOUR(D2),MINUTE(D2),1),"
In advance"," Behind")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk