Cannot think of a short title, see whole post please :)

[Lux Aeterna]

I've made an excel sheet that automatically adds working hours (arrival - departure) and overtime hours for every month.

What I'd like to do is to add overtime up to 20 hours per month in total.

In the picture below there are 22 working days, so I'd like to have a maximum of 20 days of 1 hour overtime, not 22 as it is now (yellow box).

Moreover, there are months that someone might work, for example, for 15 days. So a possible solution would be 10 days of 1 hour overtime and 5 days of 2 hours overtime.

Finally, someone might have worked for 8 days. In that case I'd like to have 2 hours overtime daily.

Max overtime per day is 2 hours, and max overtime per month is 20 hours.

Hope it's not too complicated, thank toy in advance.

PS: Wherever you see 15:00, it's the sum of the cell on the left (14:00) plus 1:00. I can add the excel file if necessary

 

[sijpie]

I am assuming the formula in your yellow cell takes the difference of each of the pairs of green (overtimestart & finish) cells and adds them all up.
Assuming the cell with Deutera is cell B2, then the first green cells are B5 and C5.
To limit the count to two hours each day you could use the MIN() function :

=MIN((C5-B5)*24,2)

To limit the total to 20 you again use the MIN() fuction. The formula in the yellow cell would then be:

=MIN(20,MIN((C5-B5)*24,2)+MIN((D5-E5)*24,2)+....+MIN((J25-K25)*24,2))

 

[Lux Aeterna]

I am assuming the formula in your yellow cell takes the difference of each of the pairs of green (overtimestart & finish) cells and adds them all up.
Assuming the cell with Deutera is cell B2, then the first green cells are B5 and C5.
To limit the count to two hours each day you could use the MIN() function :

=MIN((C5-B5)*24,2)

To limit the total to 20 you again use the MIN() fuction. The formula in the yellow cell would then be:

=MIN(20,MIN((C5-B5)*24,2)+MIN((D5-E5)*24,2)+....+MIN((J25-K25)*24,2))

Thank you for your answer! I am afraid I didn't explain what I need well enough. B5 is always equal to C4. So I would practically like C5 to be filled automatically in a manner that all differences (C5-B5, E5-D5 ... K25-J25) aggregate to 20.

For example, if (day1) C5-B5=1, (day2) E5-D5=1 ... (day19) K25-J25=1 and all these differences sum up to 19 (because someone might have worked 19 days that month), I would like the K25-J25 couple to be equal to 2 (meaning that J25=14:00 and K25=16:00).
Another example, is if someone has worked 18 days, it should be (day1) C5-B5=1, (day2) E5-D5=1 ... (day17) I25-H25=2, (day18) K25-J25=2 => total 20

Thank you in advance!

 

[Lux Aeterna]

I am posting it again with some more explanation.

Here is our monthly schedule.
- We work from Monday to Friday for 7 hours (regular). Arrival and departure times are standard, so that was the easy part.
- We are also getting a bonus for 20 more hours per month (overtime). What I need to do is to spread those 20 hours to the days we have worked automatically.

This means that if we work for 22 days, we can have any "combination" of overtime hours as long as it sums up to a total of 20. For example, we can have 20 days of 1 hour overtime (and 2 days of zero overtime), or 10 days of 2 hours overtime (and 12 days of zero overtime), or 10 days of 1 hour and 5 days of 2 hours overtime (and 7 days of zero overtime), or any other combination that sums up to 20.

Limitations
Max overtime per month is 20 hours.
Max overtime per day is 2 hours.
We can work either 1 or 2 hours of overtime (i.e. not 1:20 hours or 30 minutes).
If someone has worked for e.g. 8 days, max overtime is 8 days * 2 hours.

I can upload excel file if necessary.

Hope someone can help!

 

[Lux Aeterna]

Bump

 

[sijpie]

I'll try to have a look at this this week

 

[Lux Aeterna]

Bump

 

[sijpie]

Hi Lux, sorry for the delay. A question: do you have the latest version of Excel? (Does your Excel have the function =LET() ?)

 

[Lux Aeterna]

Hi Lux, sorry for the delay. A question: do you have the latest version of Excel? (Does your Excel have the function =LET() ?)

No worries, thanks for getting back! Unfortunately no, I think it's office 2019.

 

[sijpie]

No problem.