excel date/time formatting query: Motoring Computer Related forum

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

View Threaded

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

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

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

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

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

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

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

Forum Version 3.0 - By Khoosys