Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format duration in mm/dd hh:mm
Hi,
I need to display a duration that could last several month. I make a difference between 2 dates and I apply the format mm/dd/ hh:mm. It works fine except that it displays always 01 for the month. For axample 18/06/2009 23:03 22/06/2009 07:21 result = 01/03 08:17 where it should be 00/03 08:17 In fact Excel treat this duration as a serial date. In that case the minimun value for the month is 1 for January. Then the same problem is propagated in the chart that I make based on that column value. Any help is welcome Eric |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format duration in mm/dd hh:mm
Hi,
try d hh:mm or some other variation which meets your needs Choose Format, Cells, Number tab, Custom, and enter the above code on the Type line. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Malkowiak.work" wrote: Hi, I need to display a duration that could last several month. I make a difference between 2 dates and I apply the format mm/dd/ hh:mm. It works fine except that it displays always 01 for the month. For axample 18/06/2009 23:03 22/06/2009 07:21 result = 01/03 08:17 where it should be 00/03 08:17 In fact Excel treat this duration as a serial date. In that case the minimun value for the month is 1 for January. Then the same problem is propagated in the chart that I make based on that column value. Any help is welcome Eric |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format duration in mm/dd hh:mm
a duration that could last several month.
d hh:mm That format will only work up to 31 days, after that it rolls over. I don't know of a number format that will handle this application. AFAIK, you'd need to do something like: =INT(A2-A1)&" "&TEXT(MOD(A2-A1,1),"h:mm") Which is a text string. -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, try d hh:mm or some other variation which meets your needs Choose Format, Cells, Number tab, Custom, and enter the above code on the Type line. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Malkowiak.work" wrote: Hi, I need to display a duration that could last several month. I make a difference between 2 dates and I apply the format mm/dd/ hh:mm. It works fine except that it displays always 01 for the month. For axample 18/06/2009 23:03 22/06/2009 07:21 result = 01/03 08:17 where it should be 00/03 08:17 In fact Excel treat this duration as a serial date. In that case the minimun value for the month is 1 for January. Then the same problem is propagated in the chart that I make based on that column value. Any help is welcome Eric |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format duration in mm/dd hh:mm
To get the result in the "format" mm/dd/ hh:mm you can use this formula =TEXT(DATEDIF(A2,B2-(MOD(B2,1)<MOD(A2,1)),"m"),"00\/")&TEXT(DATEDIF(A2,B2-(MOD(B2,1)<MOD(A2,1)),"md"),"00")&TEXT(B2-A2," hh:mm") where A2 contains start time/date and B2 end time/date Like Biff's suggestion this also returns a text string..... -- barry houdini ------------------------------------------------------------------------ barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119441 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format duration in mm/dd hh:mm
Thanks all for your propositions that solve the spreadsheet presentation, but
I am also making a display of that values in a chart, and a text value cannot be managed by the chart to make the display :-( So in short Excel does not provide a format for a duration over 31 days. I thought to use a conditional formatting to treat the duration over 1 month in one way and the other in another but it does not apply to the cell format itself. It applies to the color/font/background... nothing that will touch the format of the cell itself. I am rather disappointed that Excel cannot handle my case :-( Even if I want to handle that by days I can't has after 31 the counter is back to 1!!!... The only solution is to use the date as serial value then I can have my graphic displayed correctly but the scale will be a bit strange for the users... Let me know if you have an other slolution and thanks again for your search. Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
duration | Excel Worksheet Functions | |||
How do I calculate duration entered in HH:MM-HH:MM format | Excel Discussion (Misc queries) | |||
format for duration | Excel Discussion (Misc queries) | |||
Format cell to show duration of phone calls | Excel Worksheet Functions | |||
Duration | Excel Discussion (Misc queries) |