Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month

felixstraube said:

I don't see the point in counting weeks. Why you ask? Here is a calendar for 2024:

View attachment 103738

How are you going to count the weeks where the text is red? which has some days in one month and others in the next?
That's why I suggested counting days, and multiplying them by the hours worked each day.
Now if you work 30 hours a week 5 days a week you have 6 hours per day. If you work 7 days a week that would be 4,286 hour a day. Which isn't a nice number but if you add them up i think you are going to have a more precise way of counting worked hours.
Another option is to create a list or table where you specify how many hour you work each day.
Let's say you work 30 hours a week, a distribution of hour per day could be for example:

Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total
5	5	5	5	5	5	0	30

or for 20:

Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total
4	4	4	4	2	1	1	20

or for 25 something like:

Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total
5	5	4	4	4	3	0	25

Or whatever distribution you like.
And then use one of these distributions for each worker to calculate the hours he worked in the month.
It would be easier if you just divide the hours per week by the days worked in a week, but this is another possibility.
Let me know how do you want to calculate it and I'll see what I can come up with.

Click to expand...

I really get your point that a week can be in 2 months so perhaps my approach hasnt been the best.

The issue is that in the contract we have a begin date, end date and the forcast hours agreed per week.

If we would do it per day, how then would you do it?

The issue remains we need make sure only the correct amounts of hours are calculated based on the remaining days for the begin and end date of each contract.

How would you do it? Can you create a sample for me?

I already did... working 5 days a week. But you said that you could work not only on week days, that is monday to friday, but you could work on weekends. So you have to tell me how many hour you are going to work each day.
Is it 6 days a week, evenly distributed? Or more hour some days and less others, like in my examples above?
Is it 7 days a week, evenly distributed?
Just 5 days a week?

Another thing to consider al you contracts times go from a day in the week to the same day in the week. For example for Piet you start on a monday (01/01/2024) and end on a monday (15/04/2024).
Do you work that last monday? Or do you not work that monday? Because if you do that wouldn't be a whole number of weeks.



But if you do not work that last day this formula will give you the exact number of hour worked: (# of weeks)*(hours per week) = sum of hour calculated per month:

Dear Felix,

I really appreciate that you take the time to help me and explain it so well!!!

You are correct that you gave me a perfectly working sample based on 5 days. The problem is that the people will have for example 30 hours a week as a forecast and I don't know yet how they will work these days upfront. They have 3 shifts, morning, evening and night and this is spreed out over 7 days including the weekend

So it means I just have an agreement for 30 hours a week but they can work 8 hours one day and the next day 4 hours. Then they are 2 days off and work the rest straight. So the best would be to have it based on 7 days I think, and some days have ours and some days will not have hours.

We just have to keep track of the number of days available in that month. For example Piet:
Piet's contract is from 08/01/2024 until 15/04/2024 which is 98 days and in this case divided by 7 would be 14 weeks.

From the 08th of January 2024 until the 31th of January it will be 23 days or 3,28 weeks.

So I think the best what we could do is: 23 days = 3,28 weeks times 30 hours a week which is 98,57 hours that month. And then Rounding it off to whole numbers so in this case 99 hours.

Regarding your questions: Another thing to consider al you contracts times go from a day in the week to the same day in the week. No it can change that’s why I think it would be better to round the number up. So how do I change it to 7 days a week, because we should expect 99 hours for January?

Also when I subtract the dates I get different results in Excel:


I thought we could calculate with the days; 23,28,30 and 14 but then we end up with 95 and when I take the 2 basic dates I get 98 days. Why is this?

So with your formula I get the same results as you expect April


Again I really appreciate everything you are doing for me!!! Without your help I wouldn't be able to solve this issue for sure!!!

Counting days as you said:
It is basically the same as option number 2)

Dear Felix,

Sorry for my late reply. First of all Happy new year and the best wishes for you.
Thank you for all your help, this issue is now resolved.

Thanks a lot!!!

Thanks