Rounding time to nearest 15 min

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a cell that I want to display the result of one cell subtracted from another. I then want to round the result to the nearest quarter hour increment. The cells have time formatting like this [hh]:mm. So basically in cell C1, I want to subtract cell A1 from B1 and round that to the nearest 15 min. Any help would be great. Thanks. Matt
 
One way:

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

or, since XL stores times as fractional days and there are 96 quarter
hours in a day, this is equivalent:

C1 =ROUND((B1-A1)*96,0)/96
 
One way

=ROUND((B1-A1)/(1/96),0)*1/96

--

Regards,

Peo Sjoblom


Matt said:
I have a cell that I want to display the result of one cell subtracted
Click to expand...
from another. I then want to round the result to the nearest quarter hour
increment. The cells have time formatting like this [hh]:mm. So basically
in cell C1, I want to subtract cell A1 from B1 and round that to the nearest
15 min. Any help would be great. Thanks. Matt
 
John,

Had to use

=ROUND(F20/"0:15:00",0)*"00:15:00"

on my machine for the better (IMO) option 1.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

JE McGimpsey said:
One way:

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

or, since XL stores times as fractional days and there are 96 quarter
hours in a day, this is equivalent:

C1 =ROUND((B1-A1)*96,0)/96


Matt said:
I have a cell that I want to display the result of one cell subtracted from
another. I then want to round the result to the nearest quarter hour
increment. The cells have time formatting like this [hh]:mm. So basically in
cell C1, I want to subtract cell A1 from B1 and round that to the nearest 15
min. Any help would be great. Thanks. Matt
Click to expand...
Click to expand...
 
D***, pasted garbage again,

Was aiming for "0:15:00" of course.

I prefer option 2 only because it doesn't involve two autocoercions from
text to numeric, but I agree that the intent of option 1 is much more
clear.
 
Hi JE!

But it "should" work because Excel normally interprets 00:15 as 15
minutes. DATEVALUE accepts abbreviated dates. And TIMEVALUE allows
"00:15"

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top