Rounding time to nearest 1/4 hour

[Guest]

I am setting up a time schedule and need to calculate time served during a
month. If I have:

b1:b31 = begin time (cells formatted as hh:mm)
c1:c31 = end time (cells formatted as hh:mm)
d1:d31 = total time worked (formatted as hh:mm)

Problem: how do I add d1:d31 to total time worked during month to look like
hh.mm with minutes converted to 100 units per hour and displayed as quarter
hours.

B1 = 7:15 C1 = 12:15 D1 = 7.00
B2 = 7:15 C2 = 12:30 D2 = 7.25
B3 = 7:15 C3 = 12:45 D3 = 7.50
Total hours worked = 21.75

Given: Time is entered only in quarter hours as above.

Examples: If time worked was 120 hours 45minutes, result shoud be displayed
as 120.75 and 95 hours 15 minutes should be displayed as 95.25.

In the end, minutes needs to be converted to 100 units per hour and
displayed in the quarter hour unit (25, 50, 75 or 00)

Thanks in advance.

Hank-B

 

[JE McGimpsey]

XL times are stored in fractional days, so to get fractional hours,
multiply the times by 24:

D1: =(C1-B1)*24

Format D1 as General or another number format, if necessary.

However, you must be *much* more efficient than I am. I only can do 5
hours of work between 7:15 and 12:15, not 7.

Or are you a lawyer?

 

[Guest]

Thanks Jim - that's easier than I'd imagined. I work around lawyers so it
must be wearing off - I'll have to check my sources more carefully!! Start
times are really 5:51am!!

 

[Frank Kabel]

Hi
see:
http://www.xldynamic.com/source/xld.Rounding.html#time

 

[Dave Peterson]

John, not Jim.

(I'm getting pretty good on these followups <vbg>.)

 

[JE McGimpsey]

I'm often working at 4:00 am - my client in Hong Kong usually holds off
until then before calling me...