Get workdays in a month

[Villalobos]

Hi there,

I am trying to calculate the workdays in a month according to a unique shift model, but I am stuck.

The summary:

• The hours for each shift are spread over a 6-day workweek that runs from Monday through Saturday. Bank holidays must be taken into account.​
• The 1st shift is 8 hours long. It begins at 7 o'clock in the morning. Full-time 1st shift workers spend 48 hours (6x8) on the job during their workweek.
• The 2nd shift is 8 hours, except on Saturday when it is 5 hours. Full-time 2nd shift workers spend a total of 45 hours (5x8 + 5) on the job during their workweek.
• The 3rd shift is 8 hours long, no work on the 1st, 2nd and 3rd Saturdays of the month but the 4th Saturday is 8 hours long. Full-time 3rd shift workers spend a total of 42 hours (5x8 + 8/4) on the job during their workweek.

My problem is that I do not know how to apply the 3rd shift model correctly. Do you have an idea?

Please see the attached sample file for details, shift models, current formula and expected result.

Thank you!

 

[vletm]

Villalobos​

About Your: My problem is that I do not know how to apply the 3rd shift model correctly. Do you have an idea?
What is correct way to apply it?
How their weekly totals can be 42hrs, if they have always 8hrs daily working time?
Your sample shows weekly hrs 8 and 40.
Have You tested You logic with many months?
... there could be different number of Saturdays.
Isn't there now (in Your sample) 25 working days? ( =COUNTIF(G9:G39,">0) )

 

[Villalobos]

Hi vletm,

Thanks for taking the time to help me. Have a look at the answers here.

Q1 : In this example, the correct formula (see the expected result in cell N9) should give 23.04167 days (= total of 554 working hours / 24 hours) in December 2023.

Q2 : The 3rd shift is 8 hours long, no work on the 1st, 2nd and 3rd Saturdays of the month but the 4th Saturday is 8 hours long. Full-time 3rd shift workers spend a total of 42 hours (5 days x 8 hours + 8 hours / 4 Saturdays) on the job during their workweek.

Q3 : In this example, the last week of December of the 3rd shift shows only 40 hours because the 25th is a public holiday, but the Saturday of 30th December 2023 has 8 hours of work.

Q4 : The 3rd shift works always 8 hours on the 4th Saturdays of the month.

Q5 : No, it isnt. The number of working days must be derived from the total number of working hours in the month.

 

[vletm]

Villalobos​

A1: Why Your given formula is correct? What makes it correct?
A2: ... hmm? Did You give something new?
A3: Do You mean that You'll modify Your formula monthly?
A4: There could be four or five Saturdays in every Month. ... what to do with 5th one?
A5: Do You mean that days which has less that 24hrs working are not working days?
Based Your sample file, Your named Workingdays - calculated formula (L9) should be something else if You'll try to solve Your the number of working days. ... how do it take care days which has eg 21hrs working (30-Dec-2023) ?
Where do You need formula in cell L9 ... if You have Your expected result in cell N9?

 

[vletm]

Villalobos​

I checked one ... two more times Your challenge.

I found one way to do calculations someway as You've done.
You can combine those ... if You'll need those.
BUT
In other time, I noticed one minor challenge with 3rd shifts hours with timings.
... because that shift works from 23 to 07 ... that means something else than You've written in Your file (F-column).
Check this files H-column ... eg Mondays those works normally 1hr (23-00) as well as Saturdays 7hrs (00-07).
... Do above mean that Your named one Saturday's 8hr shift makes totally 15hrs shift?
I didn't try to solve Your original challenge with those timings.

 

[Villalobos]

Thanks for the help vletm.