... round a time entry to the next "30 minute increment"...

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

Guest

I have a worksheet that I enter time values into individual cells:

A1 = 6:00
B1 = 12:00
C1 = 12:45
D1 = 17:15

When I enter these values, I would like some of the values to round to the
next higher "30 minute increment" such as Cell C1 should become 13:00.

I would also like some of the values to round back to the next lower "30
minute increment" such as Cell D1 should become 17:00.

What function should I use, or what code should I type for this.

Darrell
 
Try
=ROUND(C1*24,0)
and format the cell as a number with two decimal places
This will display 13.00 as opposed to 13:00, is that a problem? If so post
back,
Regards,
Alan.
 
=ROUNDDOWN(A1*48,0)/48


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Although, this is not elegant it should get you started, suppose D5 has your
time.
=IF(MINUTE(D5)<30,TIME(HOUR(D5),0,0),IF(D5>30,TIME(HOUR(D5)+1,0,0),D5))

Actually, the functions CEILING and FLOOR could be used, but I can't figure
it right now.
 
No, I meant ROUNDDOWN. The OP said ... I would also like some of the values
to round back to the next lower "30
.....

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Okay, now that I read the second half, I see that when to roundup/rounddown
was not specified.
My apologies, Bob
 
Aah! but it was.
The OP said round to the next higher half hour when 12:45, and round
back to 12:00 when it was 12:15
Maybe
=IF(MINUTE(A1)<30,ROUNDDOWN(A1*48,0)/48,ROUNDUP(A1*48,0)/48)
 
Geez that is confusing!

Bob


Roger Govier said:
Aah! but it was.
The OP said round to the next higher half hour when 12:45, and round
back to 12:00 when it was 12:15
Maybe
=IF(MINUTE(A1)<30,ROUNDDOWN(A1*48,0)/48,ROUNDUP(A1*48,0)/48)
Click to expand...
 
I should get off this one, but...
note that the latest function always goes to the nearest top of the hour
unless A1 is exactly the bottom of the hour, or
=IF(MINUTE(A1)=30,A1,ROUND(A1*24,0)/24)

Maybe
=IF(AND(MINUTE(A1)<=15,MINUTE(A1)>=45),ROUNDDOWN(A1*48,0)/48,ROUNDUP(A1*48,0)/48)
??
 
Hi Bob

No, I don't think that is what the OP wanted.
His request
Data 6:00 12:00 12:45 17:15
Result 6:00 12:00 13:00 17:00
He didn't supply times for 6:30 and 12:30 but he did say the rounding
should be to the 30 minutes, other than 0:15 goes down and 0:45 goes up.

My first posting
=IF(MINUTE(A1)<30,FLOOR(A1,TIME(0,30,0)),CEILING(A1,TIME(1,0,0)))
Result 6:00 12:00 13:00 17:00 7:00 13:00
both these last 2 (which I didn't test), would have been wrong.

Bob's =ROUNDDOWN(A1*48,0)/48
Result 6:00 12:00 12:30 17:00 6:30 12:30
the first 12:30 is wrong

My amendment of Bob
=IF(MINUTE(A1)<30,ROUNDDOWN(A1*48,0)/48,ROUNDUP(A1*48,0)/48)
Result 6:00 12:00 13:00 17:00 6:30 12:30
all as requested

Your latest proposal =IF(AND(MINUTE(A1)<=15,MINUTE(A1)>=45)
,ROUNDDOWN(A1*48,0)/48,ROUNDUP(A1*48,0)/48)
Result 6:00 12:00 13:00 17:30 6:30 12:30
the 17:30 time is wrong

I think on balance, Bob's formula, with my amendment just shades it<bg>
 
Back
Top