Displaying time as quarter units
Posted by Sue Rupe on November 20, 2001 10:53 AM
I need to display work hours accumulated in quarter
hour seqments. So 8 hrs 30 min (8:30)is displayed as
8.2, ten hours 15 min. is 10.1, :45 is .3 etc.
Then I need to be able to add the various quarters
to make a whole number and create a sum total.
Has someone done this before?
Posted by IML on November 20, 2001 11:29 AM
If you simply want to show your time in this format, you could put this formula
=HOUR(A1)&TEXT(CEILING(MINUTE(A1),15)/150,".0")
Next to cell A1, for instance.
You could run this along side your actual times and perform all calculations using the real time.
This could may lead to different results, such as when 7 and 2 seconds are added:
Time formula
0:07 0.1
0:02 0.1
0:09 0.1 (:09 = .01, not .1 plus .1 =2)
Another caveat, :45 would have to be enter as 0:45 with this conversion formula.
sorry if I'm way off base.
Posted by Mark W. on November 20, 2001 3:31 PM
Your sample data didn't include 10:23 or 8:08
so I wasn't quite sure how you wanted it handled...
=HOUR(A1)+INT(MINUTE(A1)/15)/10 ...always rounds down.
=HOUR(A1)+CEILING(MINUTE(A1),15)/150 ...always rounds up.
=HOUR(A1)+ROUND(MINUTE(A1)/150,1) ...rounds up at increments of 7.5.
