Rounding a Date

lindemrm
lindemrm used Ask the Experts™
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill PrewIT / Software Engineering Consultant
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 PrewIT / Software Engineering Consultant
Top Expert 2016

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


»bp
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
In this case, I'm needing to set the cutoff time to 15:00:00 Eastern.
Bill PrewIT / Software Engineering Consultant
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 PrewIT / Software Engineering Consultant
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))
IT / Software Engineering Consultant
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))

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial