I am trying to set a specific start DateTime, and end DateTime based on the current time. At work, day shift work 05:30 in the morning till 17:30 in the evening. Then night shift start at 17:30 and work until 05:30 the next morning.
Based on the current time, I want to describe the start and end DateTime of the previous shift. For example if its 21/09/2017 08:40, the start and end Date Time is 20/09/17 17:30 and 21/09/2017 05:30 respectively. Another example, if it is 21/09/2017 16:00, the start and end Date Time is 20/09/17 17:30 and 21/09/2017 05:30 respectively again (still day shift). If the current time is 21/09/2017 20:50, the start and end Date Time is 21/09/17 05:30 and 21/09/2017 17:30 respectively.
I have tried and cannot get it to work. Currently I have
In Cell A1 i have =NOW()
In Cell A2 =(INT(2*$A$1)/2)-0.5+0.2291667; [the minus 0.5 brings it to 6am or 6pm, and the 0.229 brings it back another 30min]
IN Cell A3 =A2+0.5
Right now my formula does not work after 12pm but I do not know how to change it. With the current formula if the time is 21/09/17 14:00, it calculates the Start DateTime as 21/09/2017 05:30, which is incorrect it should 20/09/17 17:30.
Bookmarks