I am creating a worksheet where employees schedule work throughout the day
eg task 1 will be start time 9am, finish time 11am. The result is 2 hours to do the task.
The problem i'm having is that I need to do a calculation that if a task includes lunch then the result takes an hour away from the task time.
eg task 2 will be start time 12pm, finish time 3pm, excel displays 3 hours, i need it to say 2 hours because 1-2pm is contractual lunch.
I can't just do a day formula that says minus 1 from total as this is task orientated and will vary depending on start and finish times of each task.
I hope someone can help.
PS, it needs to be a formula and not vba as we have a problem with macro's if multiple people use the worksheet.
The formual below assumes the start time is in cell A1 and the end time is in B1. It also assumes that if the start time or end time fall between 1 and 2, then 1 hour is deducted, I'm not sure this is true if the start time is 1:30pm?
Both formula's seem to work EXCEPT I have the same problem in both
If I put a task in from 1pm to 3pm your calculations are correct i get 1 hour yippee
However if I put in a task from 2pm to 3pm it is now coming up with 0 hours, this is incorrect as lunch is 1pm-2pm. I tried to play with both formula's to say 1:59:00 but this is not working.
It counts all hours between the two times except those between 13:00 and 14:00, even if start or end times are between those 2, e.g. 13:20 to 14:20 will give a result of 0:20.
Formula only works when A1 and B1 are on the same day, i.e. B1 is greater than A1.....
Note: that your times all appear to be 1 second past the hour, I don't know if that's deliberate, so for row 15 my formula will give you the number of hours between the end of lunch, i.e. 14:00:00 and the end time in C15 16:00:01, i.e 2 hours and 1 second which converts to approx 2.000278
Bookmarks