rounding time to 15 minute interval

C

Colm O'Brien

I want to use excel to record a start time and end time
then subtract one from the other and round to the nearest
quarter hour for billing purposes

any ideas?
 
J

Jason Morin

A1: start time
B1: end time
C1: =ROUND((B1-A1)/"00:15",0)*"00:15"

Format C1 as time format 13:30 under Format > Cells >
Number Tab > Time.

HTH
Jason
Atlanta, GA
 
J

J.E. McGimpsey

One way:

A1: <start time>
A2: <end time>
A3: =ROUND((A2-A1)/TIME(0,15,0),0)*TIME(0,15,0)
A4: Rate
A5: =A3*24*A4

XL stores times as fractional days, so to convert hh:mm to hours,
multiply by 24.

You could make A3 a bit more efficient by converting to constants:

A3: =ROUND((A2-A1)*96,0)/96

Note, you may have to format A3 as a time.
 
C

Colm O'Brien

-----Original Message-----
A1: start time
B1: end time
C1: =ROUND((B1-A1)/"00:15",0)*"00:15"

Format C1 as time format 13:30 under Format > Cells >
Number Tab > Time.

HTH
Jason
Atlanta, GA

.
Click to expand...
 
Top