Solved I am trying to calculate time based on date. i have to put n

January 22, 2013 at 02:06:09
Specs: Windows XP
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/2013

Please help


See More: I am trying to calculate time based on date. i have to put n

Report •


#1
January 22, 2013 at 06:47:27
✔ Best Answer
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.


Report •

#2
January 22, 2013 at 11:46:45
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


Report •
Related Solutions


Ask Question