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

[Wishmaster40]

Dear all,

I am stuk with Excel and i need your help. What i am trying to achieve is the based on two dates.

The start of a contract and the end of a contract verify if the month is valid.

Name Start contract End contract Contract weeks Hours a week
Piet 01/01/2024 15/04/2024 15 30
Kees 15/01/2024 04/03/2024 7 20
Jan 08/10/2024 31/12/2024 12 25

If this is the case then i need to know the number of weeks in the month and multiply this but the hours in spend per week.

I have used this formula: =ISOWEEKNUM((DATE(YEAR(C2);1;DAY(C2-WEEKDAY(C2;2)))))

Month Month/year Start month Number of weeks per month
1 Jan/24 01/01/2024 5
2 Feb/24 01/02/2024 4
3 Mrt/24 01/03/2024 4
4 Apr/24 01/04/2024 5
5 May/24 01/05/2024 4
6 Jun/24 01/06/2024 4
7 Jul/24 01/07/2024 5
8 Aug/24 01/08/2024 4
9 Sep/24 01/09/2024 4
10 Oct/24 01/10/2024 5
11 Nov/24 01/11/2024 4
12 Dec/24 01/12/2024 4

In the months i want to have the hours spend.

Now based on the date of the first contract i have 5 weeks in Jan/24, 4 in feb/24, 4 in mrt/24 and despite April/24 has 5 weeks the contract is only for 2 weeks. So thats why 2 times 30.

Jan/24 Feb/24 Mrt/24 Apr/24 May/24 Jun/24 Jul/24 Aug/24 Sep/24 Oct/24 Nov/24 Dec/24
5*30. 4*30. 4*30. 2*30

And for the other months i dont need a calculation or course.

How do i validate that the month for example jan24 is part of the contract duration time and how do i assign this to the field and calculate the right a mouth based on the weeks. And considering the end date of the contract doesnt apply for the full number of weeks.

Thanks for your help!

 

[felixstraube]

Why do you need to calculate by weeks?
If you need the hour spend per month why not use the hour per day and multiply by the days worked in the month?

 

[Wishmaster40]

Why do you need to calculate by weeks?
If you need the hour spend per month why not use the hour per day and multiply by the days worked in the month?

I have the contract hours per week thats is why. But per day is also fine, if i can assign it to the right month then

 

[felixstraube]

How about?:

Validate month.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Name	Start	End	weeks	Hours a week		ene-24	feb-24	mar-24	abr-24	may-24	jun-24	jul-24	ago-24	sep-24	oct-24	nov-24	dic-24
2	Piet	01/01/2024	15/04/2024	15	30		138	126	126	66
3	Kees	15/01/2024	04/03/2024	7	20		52	84	8
4	Jan	08/10/2024	31/12/2024	12	25											90	105	110
Hoja1

Cell Formulas
Range	Formula
G2:R4	G2	=LET(d,NETWORKDAYS(MAX($B2,G$1),MIN($C2,EOMONTH(G$1,0)))*$E2/5, IF(d>=0,d,"") )

 

[Wishmaster40]

How about?:

Validate month.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Name	Start	End	weeks	Hours a week		ene-24	feb-24	mar-24	abr-24	may-24	jun-24	jul-24	ago-24	sep-24	oct-24	nov-24	dic-24
2	Piet	01/01/2024	15/04/2024	15	30		138	126	126	66
3	Kees	15/01/2024	04/03/2024	7	20		52	84	8
4	Jan	08/10/2024	31/12/2024	12	25											90	105	110
Hoja1

Cell Formulas
Range	Formula
G2:R4	G2	=LET(d,NETWORKDAYS(MAX($B2,G$1),MIN($C2,EOMONTH(G$1,0)))*$E2/5, IF(d>=0,d,"") )

Thanks for the reply but, this will not work i am afraid, Attached you can see the Excel file.

Excel Help Forum

I hope this will help and perhaps you can help me fix it.

 

[felixstraube]

Why would that not work?

I modified your file and uploaded it here:

Issue new-5 (edited Felix).zip

 

[Wishmaster40]

Why would that not work?

I modified your file and uploaded it here:

Issue new-5 (edited Felix).zip

I think you are close. The issue is that the working hours are per week, because it can be a morning, evening or night shift and also during the weekend.

So how could we fix this, because with your formula, we calculate the normal working days right?

 

[felixstraube]

Yes i was dividing by 5 the hour in the week. But we can fix this. So how many hour are you working each day?

 

[Wishmaster40]

so how many hour are you working each day?

The people work for example 24 hours a week or 32 hours or 21 hours it is different per person and per contract.

Now i want to calculate based on the contract which have different start en end date in the middle of the month.

Name	Start contract	End contract	Contract weeks	Hours a week
Piet	08/01/2024	15/04/2024	14	30
Kees	15/01/2024	04/03/2024	7	20
Klaas1	08/10/2024	31/12/2024	12	25
Laura	16/01/2024	31/12/2024	50	32

So for example for Piet, need the right hours for Jan/24, Feb/24,Mrt/24 and April/24. On the second sheet i have found a formula that calculates the weeks, but dont know if it is correct yet. So you can see that we need different weeks for Jan/24, Feb/24,Mrt/24 and April/24. and then times the hours, of course we can do it also based on days but then we need take the complete week i think or not?

Month	Month/year	Start month	Number of weeks per month
1	Jan/24	01/01/2024	5
2	Feb/24	01/02/2024	4
3	Mar/24	01/03/2024	4
4	Apr/24	01/04/2024	5
5	May/24	01/05/2024	4
6	Jun/24	01/06/2024	4
7	Jul/24	01/07/2024	5
8	Aug/24	01/08/2024	4
9	Sep/24	01/09/2024	4
10	Oct/24	01/10/2024	5
11	Nov/24	01/11/2024	4
12	Dec/24	01/12/2024	4
			52

So like this but then correct:

Name	Start contract	End contract	Contract weeks	Hours a week		Jan/24	Feb/24	Mar/24	Apr/24	May/24	Jun/24	Jul/24	Aug/24	Sep/24	Oct/24	Nov/24	Dec/24
Piet	08/01/2024	15/04/2024	14	30	23	5	4	4	5	0	0	0	0	0	0	0	0
Kees	15/01/2024	04/03/2024	7	20	29	5	4	4	0	0	0	0	0	0	0	0	0
Klaas1	08/10/2024	31/12/2024	12	25	31	0	0	0	0	0	0	0	0	0	5	4	4
Laura	16/01/2024	31/12/2024	50	32	15	5	4	4	5	4	4	5	4	4	5	4	4

Because now for Piet it writes 5 weeks for Jan/24 but this is not correct and the same for the other cells

 

[felixstraube]

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

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.