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

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:

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

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
--
macropod
[MVP - Microsoft Word]
-------------------------

"Stefi" wrote in message ...
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:

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

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(DAT E(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(DA TE(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


Replace @mailinator.com with @tiscali.co.uk


"Jo" wrote in message
...
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

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).
--
David Biddulph

"Sandy Mann" wrote in message
...
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(DAT E(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(DA TE(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


Replace @mailinator.com with @tiscali.co.uk


"Jo" wrote in message
...
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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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),D ATE(HOUR(D2),MINUTE(D2),1)),MAX(DATE(HOUR(C2),MINU TE(C2),1),DATE(HOUR(D2),MINUTE(D2),1)),"y")<1,"s
","
")&DATEDIF(MIN(DATE(HOUR(C2),MINUTE(C2),1),DATE(HO UR(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),MIN UTE(C2),1),DATE(HOUR(D2),MINUTE(D2),1)),"ym")<1," s
"," ")&IF(DATE(HOUR(D2),MINUTE(D2),1)DATE(HOUR(C2),MI NUTE(C2),1)," In
Advance"," Behind")

--
Regards,

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


Replace @mailinator.com with @tiscali.co.uk

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),MI NUTE(C2),1),DATE(HOUR(D2),MINUTE(D2),1)),"y"),DATE DIF(MIN(DATE(HOUR(C2),MINUTE(C2),1),DATE(HOUR(D2), MINUTE(D2),1)),MAX(DATE(HOUR(C2),MINUTE(C2),1),DAT E(HOUR(D2),MINUTE(D2),1)),"ym"),0),"hh:mm")&IF(DAT E(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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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),D ATE(HOUR(D2),MINUTE(D2),1)),MAX(DATE(HOUR(C2),MINU TE(C2),1),DATE(HOUR(D2),MINUTE(D2),1)),"y")<1,"s
","
")&DATEDIF(MIN(DATE(HOUR(C2),MINUTE(C2),1),DATE(HO UR(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),MIN UTE(C2),1),DATE(HOUR(D2),MINUTE(D2),1)),"ym")<1," s
"," ")&IF(DATE(HOUR(D2),MINUTE(D2),1)DATE(HOUR(C2),MI NUTE(C2),1)," In
Advance"," Behind")

--
Regards,

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


Replace @mailinator.com with @tiscali.co.uk