|
Calculating age differences
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 |
Calculating age differences
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 |
Calculating age differences
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 |
Calculating age differences
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 |
Calculating age differences
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 |
Calculating age differences
"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 |
| All times are GMT +1. The time now is 02:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com