Help With Time Formatting

[Erehwon]

I want to format a number such as 33.15 to appear as days, hours, minutes,
i.e. 33:03:36, or similar. So far, I've been unable to come up with a
custom format that will do that.

 

[Bob Phillips]

You won't, because 33 hours is held not as 33 but as 1.375, that is 33
divided by 24. So you need to divide by 24 first then format as [h]:mm:ss.

 

[Erehwon]

Perhaps I should clarify. I'm trying to get the difference between two
dates (greater than 30 days apart) such as 33.15 DAYS (not hours) to format
as 33:03:36:00 (don't really need the seconds though). I understand that I
can use [h] to get the cumulative hours, but I want to get cumulative days.
Perhaps there's no way of doing that in Excel without using two cells, one
for the days INT(33.15) and then the other for hh:mm ? Just seemed a simple
enough format that I'd thought there'd be some way to do that in Excel.

You won't, because 33 hours is held not as 33 but as 1.375, that is 33
divided by 24. So you need to divide by 24 first then format as [h]:mm:ss.

--

HTH

Bob

I want to format a number such as 33.15 to appear as days, hours, minutes,
i.e. 33:03:36, or similar. So far, I've been unable to come up with a
custom format that will do that.

 

[Bob Phillips]

Unfortunately, you have a big problem there. The reason is that you could
take one date from another and format as d:hh:mm, but because the maximum
number of days in a month is 31, the d part of that format will cycle round
after 31, that is 33 would show as 2.

So you have to build it piecemeal, this should do it

=TEXT(INT(A2)-INT(A1)-(MOD(A1,1)>MOD(A2,1)),"0")&TEXT((MOD(A1,1)>MOD(A2,1))+(MOD(A2,1)-MOD(A1,1)),":hh:mm:ss")

--

HTH

Bob

Perhaps I should clarify. I'm trying to get the difference between two
dates (greater than 30 days apart) such as 33.15 DAYS (not hours) to
format as 33:03:36:00 (don't really need the seconds though). I
understand that I can use [h] to get the cumulative hours, but I want to
get cumulative days. Perhaps there's no way of doing that in Excel without
using two cells, one for the days INT(33.15) and then the other for hh:mm
? Just seemed a simple enough format that I'd thought there'd be some way
to do that in Excel.

You won't, because 33 hours is held not as 33 but as 1.375, that is 33
divided by 24. So you need to divide by 24 first then format as
[h]:mm:ss.

--

HTH

Bob

I want to format a number such as 33.15 to appear as days, hours,
minutes, i.e. 33:03:36, or similar. So far, I've been unable to come up
with a custom format that will do that.

 

[Ron Rosenfeld]

I want to format a number such as 33.15 to appear as days, hours, minutes,
i.e. 33:03:36, or similar. So far, I've been unable to come up with a
custom format that will do that.

You cannot do that with formatting unless the number is less than 32.

You could use a formula to separate out the days:

=INT(A1)&":"&TEXT(MOD(A1,1),"hh:mm:ss")

This will be a text string, and not a number, but will display as you request.
--ron