Rounding dates based on time in Excel

May 30, 2013 7:29 AM

I am looking for a way to take a date and time entry in Excel 2010 and round it up to the next day if the time is after a certain value.

For example, consider how, at the bank, if you deposit a check after 2pm, the deposit is counted as the next day. This is basically what I want to do: enter in the real date/time and get back the date that "counts" according to the rule.

I have found information on rounding dates to the next month, and times to the next 15- minute interval, but nothing on rounding the DATE based on the TIME. Help?

Extra bonus if this method will round up to the next BUSINESS day.

posted by oblique red to Computers & Internet (3 answers total)

Can you do something conditional by splitting date and time into separate columns, like

=if([time is after 2 p.m.], date+1, date)

posted by supercres at 7:40 AM on May 30, 2013

Best answer: =IF(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))>TIME(14,0,0),WORKDAY(A1,1),WORKDAY(A1,0))

should work, where time takes the hour, minute, second and A1 is the cell with the day/time you are looking at. Basic checking showed this works, though it may not work properly if the day is a weekend or over holidays.
posted by jeather at 7:40 AM on May 30, 2013 [2 favorites]

I think @jeather's answer should work.
I came up with:

=IF(INT(TEXT(A1, "hh")) >= 14, A1 + 1, A1)

.. which seemed to work for me (I had to coerce the output to a date using the formatter (press ctrl+1))
posted by richb at 7:42 AM on May 30, 2013