Rounding time to nearest 15 min

[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

 

[JE McGimpsey]

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

 

[Peo Sjoblom]

One way

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

--

Regards,

Peo Sjoblom

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

 

[Bob Phillips]

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)

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

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

 

[JE McGimpsey]

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.

 

[Bob Phillips]

Which is why I prefer it as many discussions with Harlan (sic!) attest.

Bob

 

[Norman Harker]

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.