I am creating a work schedule and have 3 columns; one for Start Time (A2), one for Stop Time (C2) and one for Hours worked (D2).
In the Stop Time column i am needing the option to put in the word "Close" to represent a defined time (lets say 6 PM) instead of typing in an actual time like 6:00 PM.
The following formula is what im using now and it works perfectly!
=SUM(IF(C2="CLOSE","18:00",C2)-IF(A2="","0",A2)+(C2<A2))*24
^
^
used to calculate time if after midnight
However, I am now needing to take out 30 mins for lunch if the resulting sum of the above formula is = or > 5.
I Know the formula for taking out the 30 mins required is: =(IF(C2*24-A2*24=>5,C2*24-A2*24,C2*24-A2*24-.5)).
But how do i..or is it possible to have both of these IF statments in the same cell (D2) with C2 keeping the value of "18:00" if it is populated with "Close"? Or is there an easier way to subtract the 30 mins from my original formula if the sum is over 5 hours?
Thanks in advance for any assistance!
Bookmarks