How do I determine the time used on a task where the starting time is 12:30 AM? fhere is also time loss to be taken into account (subtracted from time elapsed) between the two times of work.
EG: starting time 12:30 AM (Cell A2), The end time is 8:45 AM (Cell A4), an a 15 minute break is taken(Cell A3).
Depends on how you enter the 15 minutes break… assuming this is what you have
12:30 AM (or 00:30 for everyone else)00:15
8:45 AM (or 08:45)then the formula is simply =A4-A2-A3 formatted as duration [HH]:MM => 08:00
If instead the break value in A3 is entered as minutes, i.e. 15 then the value needs to be divided by minutes per day (1440) and the formula would be =A4-A2-A3/1440.
Note that if a time span (shift) can cross midnight (i.e. the end time is smaller than the start time) you need to take that into account and add 1 for that case, so =A4-A2-A3+(A2>A4) (where A2>A4 results in 1 if the condition is true and 0 else).
Thank You! I tried to format the A3 figure as time and that caused problems. The division by 1440 is very clever, indeed!
I tried the solution to end time < start time and got no number 1 or 0. I am using the Mac version. True statements come out as “TRUE”.
Yes, if you just write the formula =A2>A4 to a cell then of course the result is boolean TRUE or FALSE, where FALSE is the boolean format for 0 and TRUE the boolean format for anything else (here 1).
Thank you for the follow-up!