Link to home
Create AccountLog in
Avatar of lindemrm
lindemrmFlag for United States of America

asked on

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?
Avatar of Bill Prew
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
Avatar of lindemrm

ASKER

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)))

Open in new window



»bp
Or more compact:

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

Open in new window


»bp
SOLUTION
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks Guys!
Just realised you could make it even smaller like this...

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