Below is a formula that will do the following:
Given an employees work day start and end times, the actual date/time of the project start and finish, and the number of 15-minute breaks taken during the project duration, the output will be the number of hours worked (this works well for any project whether it's less than a day or many days):
=((EmpEnd-MOD(ProjStart,1))+IF((CEILING(ProjEnd,1)-FLOOR(ProjStart,1))>2,((CEILING(ProjEnd,1)-FLOOR(ProjStart,1))-2)*(EmpEnd-EmpStart),0)+IF((CEILING(ProjEnd,1)-FLOOR(ProjStart,1))>1,MOD(ProjEnd,1)-EmpStart,0))*24-0.25*breaks
Here are the limitations and constraints:
- I assume that all project start/end times will be within the work day start and end times (i.e., if the employee starts at 8am, the project cannot start at 7am)
- This version does not consider weekends or holidays
If this formula works for you as-is, you are welcome to use it without hiring me. If you would like to make changes, or if it needs to consider additional requirements, let me know. I do this as a hobby.