Computer Related > excel date/time formatting query Miscellaneous
Thread Author: borasport Replies: 6

 excel date/time formatting query - borasport
I have an excel spreadsheet where columns A,B, are both in date time format and column C is B-A.
Column C is displayed as the ddd.dd i.e. number of days as a decimal - is there any way I can format it as either hours and minutes or days,hours and minutes ?
everything I try seems to generate output that is plainly wrong - i.e. 133.0597 (days) displays as 12:01 hours in hh:mm format and 12:01:26 in dd:hh:mm format
any ideas ?
 excel date/time formatting query - spamcan61
I thought that would be easy using the DATEVALUE function, but I've had a quick play and can't get it to do what you want, will have another bash later.
 excel date/time formatting query - civic_duty
Is 133.0597 (days) supposed to be 133 days and 1hr 26 mins?
 excel date/time formatting query - borasport
>> Is 133.0597 (days) supposed to be 133 days and 1hr 26 mins?

yes
 excel date/time formatting query - civic_duty
In that case you could use a combination of floor and mod functions.
eg cell C1 = 133.0597
cell C2 = MOD(C1,1)*24 [this gives decimal hours)
cell C3 = MOD(C2,1)*60 [decimal mins]

For the 133 days use floor (c1,1)
for the 1 hour, use floor (c2,1)
for the 26 mins, take cell 3 and round as needed.

you could display these in seperate cells or concatenate

Bit messy but it works, I'm sure there is a more elegant way.
 excel date/time formatting query - borasport
>> Bit messy but it works I'm sure there is a more elegant way.
>>
yeah - I'm sure I'm not the first or only person to want to do this, seems a predictable requirement, you'd expect some easily found functionality to do it !
 excel date/time formatting query - John H
>> yeah - I'm sure I'm not the first or only person to want to do
>> this seems a predictable requirement you'd expect some easily found functionality to do it !
>>

plenty of solutions on excel forums for this problem.
Latest Forum Posts