We help IT Professionals succeed at work.

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

Bill PrewTest your restores, not your backups...
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

Author

Commented:
That doesn't allow me to select a time at which I want the date to round up.
Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

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


»bp

Author

Commented:
In this case, I'm needing to set the cutoff time to 15:00:00 Eastern.
Bill PrewTest your restores, not your backups...
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)))

Open in new window



»bp
Bill PrewTest your restores, not your backups...
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)

Open in new window


»bp
=IF(MOD(A1,1)>TIME(15,0,0), ROUNDUP(A1, 0), ROUNDDOWN(A1,0))
Test your restores, not your backups...
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)

Open in new window


»bp

Author

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

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