# Rounding a Date

I'm needing to round a date based on the timestamp within the field.  Right now, I have say "12/08/17 16:42:05" in a given cell.  I need to round this date to either 12/08/17 or 12/09/17, based on the time.  So if an order came in at say 3pm (15:00:00), I need the date to round to the next day, since that's after hours. Make sense?
Bill Prew

You should just be able to use ROUND(), so if the date is in A1 then use this (and format the result column as just date, no time if you don't want to see the 00:00:00 time on the end):

=ROUND(A1,0)

»bp

That doesn't allow me to select a time at which I want the date to round up.
Okay, so what would the cutoff time be?

»bp

In this case, I'm needing to set the cutoff time to 15:00:00 Eastern.
Okay, give this a try:

=IF(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))>=TIME(15,0,0),DATE(YEAR(A1),MONTH(A1),DAY(A1))+1,DATE(YEAR(A1),MONTH(A1),DAY(A1)))

»bp
Or more compact:

=TRUNC(A1)+IF(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))>=TIME(15,0,0),1,0)

»bp
SOLUTION
Wayne Taylor (webtubbs)

membership
Create an account to see this answer
Signing up is free. No credit card required.