Format duration in mm/dd hh:mm

[Malkowiak.work]

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

 

[Shane Devenshire]

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.

 

[T. Valko]

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.

 

[barry houdini]

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.....

 

[Malkowiak.work]

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