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?
Microsoft ExcelMicrosoft Office
Last Comment
Wayne Taylor (webtubbs)
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
»bp
ASKER
In this case, I'm needing to set the cutoff time to 15:00:00 Eastern.
Okay, give this a try:
»bp
=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:
»bp
=TRUNC(A1)+IF(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))>=TIME(15,0,0),1,0)
»bp
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks Guys!
Just realised you could make it even smaller like this...
=INT(A1+1-TIME(15,0,0))
=INT(A1+1-TIME(15,0,0))
=ROUND(A1,0)
»bp