Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate duration (#yrs., # mos.)
Sorry if this has already been posted/answered; but I'm not having any luck
finding it! What I need to do is calculate the duration of a condition in years and months. i.e. Start = 6/1/01; End = 9/30/03 would return 2 yrs., 4 mos. To complicate matters further, certain conditions are necessary in rtegards to the number of months. If the remaining number of days total between 0 and 14, it should round down to the lesser whole month; between 15 and 28, I need the number of months to return a ".5" at the end; between 29 and 30, it should round up to the next whole month. Therefore, Start = 6/1/01; End = 9/9/03 would return "2 yrs., 3 mos." Start = 6/1/01; End = 9/19/03 would return "2 yrs., 3.5 mos." Start = 6/1/01; End = 9/29/03 would return "2 yrs., 4 mos." Hope I explained this correctly! I'm using Excel 2003. Thanks in advance for any help!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate duration (#yrs., # mos.)
Hi,
With the earlier date in a1 try this =DATEDIF(A1,B1,"y")&" yrs "&IF(DATEDIF(A1,B1,"md")<15,DATEDIF(A1,B1,"ym"),IF (DATEDIF(A1,B1,"md")<29,DATEDIF(A1,B1,"ym")+0.5,DA TEDIF(A1,B1,"ym")+1))&" Mths" Mike "CherylC" wrote: Sorry if this has already been posted/answered; but I'm not having any luck finding it! What I need to do is calculate the duration of a condition in years and months. i.e. Start = 6/1/01; End = 9/30/03 would return 2 yrs., 4 mos. To complicate matters further, certain conditions are necessary in rtegards to the number of months. If the remaining number of days total between 0 and 14, it should round down to the lesser whole month; between 15 and 28, I need the number of months to return a ".5" at the end; between 29 and 30, it should round up to the next whole month. Therefore, Start = 6/1/01; End = 9/9/03 would return "2 yrs., 3 mos." Start = 6/1/01; End = 9/19/03 would return "2 yrs., 3.5 mos." Start = 6/1/01; End = 9/29/03 would return "2 yrs., 4 mos." Hope I explained this correctly! I'm using Excel 2003. Thanks in advance for any help!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate duration (# yrs., # mos.)
By george, I think you've got it - thanks so much! My head was spinning
trying to figure that out!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate duration day +1 | Excel Worksheet Functions | |||
Calculate duration day +1 | Excel Worksheet Functions | |||
Calculate duration day +1 | Excel Worksheet Functions | |||
Calculate duration in Excel 2000 | Excel Worksheet Functions | |||
Calculate Duration. | Excel Discussion (Misc queries) |