• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Move to next day after 12 midnight

akumaba024

New Member
Trying to add a formula to move the date to the next day after 12 midnight. Tried to put it in 24hour format so that it would try to read that if the time is >=7:00 then it would stay at 5/14 and move to the next day if it reads a time less than 7:00.

TIA


67938
 


sorry for the confusion ..
the date would be determined by the start time .. our "work day" basically starts at 7am and ends at 2am. So if someone inputs a start time of 8am for example, then that would still fall for the current day, and 12am 1am & 2am should fall under the next day. hope I made a better explanation this time :D

67939
 
Presuming your dates are in Col A ... in A2 enter the current date.

In A3 paste this formula :
Code:
=IF(B2="","",IF(B2=(--"23:00"),A2+1,A2))

Then drag that down the column as far as needed.
 
Another take on the problem might be to accept any standard datetime but to report the work-date using the formula
= INT(datetime) + (MOD(datetime, 1) >= TIME(21,0,0))
where the green represents the date (with time information stripped out) and the green increments the reported date by one day for times later than 9pm.
 
Presuming your dates are in Col A ... in A2 enter the current date.

In A3 paste this formula :
Code:
=IF(B2="","",IF(B2=(--"23:00"),A2+1,A2))

Then drag that down the column as far as needed.
Presuming your dates are in Col A ... in A2 enter the current date.

In A3 paste this formula :
Code:
=IF(B2="","",IF(B2=(--"23:00"),A2+1,A2))

Then drag that down the column as far as needed.


Hi .. this is what I got

67959
 
Back
Top