We help IT Professionals succeed at work.

# Rounding a Date

on
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?
Comment
Watch Question

## View Solutions Only

Expert of the Year 2019
Top Expert 2016

Commented:
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

Commented:
That doesn't allow me to select a time at which I want the date to round up.
Expert of the Year 2019
Top Expert 2016

Commented:
Okay, so what would the cutoff time be?

»bp

Commented:
In this case, I'm needing to set the cutoff time to 15:00:00 Eastern.
Expert of the Year 2019
Top Expert 2016

Commented:
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
Expert of the Year 2019
Top Expert 2016

Commented:
Or more compact:

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

»bp
Commented:
=IF(MOD(A1,1)>TIME(15,0,0), ROUNDUP(A1, 0), ROUNDDOWN(A1,0))
Expert of the Year 2019
Top Expert 2016
Commented:
Can I steal the MOD() Wayne, please... :-)

So that could then be in my approach:

``````=TRUNC(A1)+IF(MOD(A1,1)>=TIME(15,0,0),1,0)
``````

»bp

Commented:
Thanks Guys!

Commented:
Just realised you could make it even smaller like this...

=INT(A1+1-TIME(15,0,0))