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

New Tue 16 Mar 10 08:49 
 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 ?
Reply to this message | Report message
       
New Tue 16 Mar 10 11:03 
 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.
Reply to this message | Report message
       
New Tue 16 Mar 10 11:14 
 excel date/time formatting query - civic_duty
Is 133.0597 (days) supposed to be 133 days and 1hr 26 mins?
Reply to this message | Report message
       
New Tue 16 Mar 10 11:25 
 excel date/time formatting query - borasport
>> Is 133.0597 (days) supposed to be 133 days and 1hr 26 mins?

yes
Reply to this message | Report message
       
New Tue 16 Mar 10 11:31 
 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.
Reply to this message | Report message
       
New Tue 16 Mar 10 11:50 
 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 !
Reply to this message | Report message
       
New Tue 16 Mar 10 21:01 
 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.
Reply to this message | Report message
       
Latest Forum Posts