Hello,
i'm having difficulty with a formula or even several formulas to calculate normal worked hours, overtime and total.
Normal working hours are 8 hours from 08:00 till 16:30, lunch is 30 minutes.
I would like to avoid having to make an additional column for lunch time, so ideally the "hours" part of the timesheet should look like this.
Capture.PNG
So in this case normal hours are 08:00-16:30, which include 30 minutes lunch (non-paid), 16:30-19:00 - 2.5 hrs of overtime
My initial formula for Normal hours was: IF(((G12-F12)*24)>8,8,(G12-F12)*24) but I just don't understand how to incorporate lunch here
Formula for overtime was like this: IF((G12-F12)*24>8, (G12-F12)*24-8, 0)
Also, the formula has to flexible, because the "normal" hours amount can vary depending on the project.
I would really appreciate help in creating correct formulas.
thanks in advance!
Bookmarks