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

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.
<< Prev | Next >>
Reply to this message | Report message
       
 Messages Author Date
 excel date/time formatting query new borasport 16 Mar 10 08:49
 excel date/time formatting query new spamcan61 16 Mar 10 11:03
 excel date/time formatting query new civic_duty 16 Mar 10 11:14
 excel date/time formatting query new borasport 16 Mar 10 11:25
 excel date/time formatting query new civic_duty 16 Mar 10 11:31
 excel date/time formatting query new borasport 16 Mar 10 11:50
 excel date/time formatting query new John H 16 Mar 10 21:01
Latest Forum Posts