I am trying to calculate time based on date. i have to put next day if the time exceed 5:30 PM. if not then same date will be there..

Ex. 2/12/2013 5:31 3/12/2013Please help

How about this: =IF(OR(HOUR(A1)>17,AND(HOUR(A1)=17,MINUTE(A1)>30)),A1+1,A1)

Format the cell with the formula as Date to display just the date.

If the time is from 6:00PM to 11:59PM, the HOUR will be greater than 17 (5PM) so add 1 to the date.

OR

If the time is from 5:31PM to 5:59PM, the HOUR will equal 17 AND the MINUTE will be greater than 30, so add 1 to the date.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi DerbyDad Another approach would be

=IF(MOD(A1,1)>17.5/24,A1+1,A1)

Where MOD(A1,1) gets everything after the decimal which represents the time and 17.5/24 = 5:30 PM

Ask Your Question

Weekly Poll