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!

 

[Wishmaster40]

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.

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?

 

[felixstraube]

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.

dom.	lun.	mar.	mié.	jue.	vie.	sáb.
31/12/2023	01/01/2024	02/01/2024	03/01/2024	04/01/2024	05/01/2024	06/01/2024
07/01/2024	08/01/2024	09/01/2024	10/01/2024	11/01/2024	12/01/2024	13/01/2024
14/01/2024	15/01/2024	16/01/2024	17/01/2024	18/01/2024	19/01/2024	20/01/2024
21/01/2024	22/01/2024	23/01/2024	24/01/2024	25/01/2024	26/01/2024	27/01/2024
28/01/2024	29/01/2024	30/01/2024	31/01/2024	01/02/2024	02/02/2024	03/02/2024
04/02/2024	05/02/2024	06/02/2024	07/02/2024	08/02/2024	09/02/2024	10/02/2024
11/02/2024	12/02/2024	13/02/2024	14/02/2024	15/02/2024	16/02/2024	17/02/2024
18/02/2024	19/02/2024	20/02/2024	21/02/2024	22/02/2024	23/02/2024	24/02/2024
25/02/2024	26/02/2024	27/02/2024	28/02/2024	29/02/2024	01/03/2024	02/03/2024
03/03/2024	04/03/2024	05/03/2024	06/03/2024	07/03/2024	08/03/2024	09/03/2024
10/03/2024	11/03/2024	12/03/2024	13/03/2024	14/03/2024	15/03/2024	16/03/2024
17/03/2024	18/03/2024	19/03/2024	20/03/2024	21/03/2024	22/03/2024	23/03/2024
24/03/2024	25/03/2024	26/03/2024	27/03/2024	28/03/2024	29/03/2024	30/03/2024
31/03/2024	01/04/2024	02/04/2024	03/04/2024	04/04/2024	05/04/2024	06/04/2024
07/04/2024	08/04/2024	09/04/2024	10/04/2024	11/04/2024	12/04/2024	13/04/2024
14/04/2024	15/04/2024	16/04/2024	17/04/2024	18/04/2024	19/04/2024	20/04/2024

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:

Issue new-5.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Name	Start contract	End contract	Contract weeks	Hours a week	Control		Control	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	08/01/2024	15/04/2024	14	30	420	=	420	108	126	126	60
3	Kees	15/01/2024	04/03/2024	7	20	140	=	140	52	84	4
4	Klaas1	08/10/2024	31/12/2024	12	25	300	=	300										90	105	105
5	Laura	16/01/2024	31/12/2024	50	32	1600	=	1600	76,8	134,4	134,4	140,8	147,2	128	147,2	140,8	134,4	147,2	134,4	134,4
Sheet1 (2)

Cell Formulas
Range	Formula
F2:F5	F2	=D2*E2
G2:G5	G2	=IF(F2=H2," = ", " <> ")
H2:H5	H2	=SUM(I2:T2)
I2:T5	I2	=LET(d,NETWORKDAYS(MAX($B2,I$1),MIN($C2-1,EOMONTH(I$1,0)))*$E2/5, IF(d>=0,d,"") )
D2:D5	D2	=(C2-B2)/7

 

[Wishmaster40]

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!!!

 

[felixstraube]

Ok last attempt. Here you can choose which one you like more.
For the option 4) to work you will have to keep column I (which is hidden now)

Issue new-5.xlsx
	A	B	C	D	E	F	G	H		J	K	L	M	N	O	P	Q	R	S	T	U
1	1) 5 days a week
2	Name	Start contract	End contract	Contract weeks	Hours a week	Control		Control		ene-24	feb-24	mar-24	abr-24	may-24	jun-24	jul-24	ago-24	sep-24	oct-24	nov-24	dic-24
3	Piet	08/01/2024	15/04/2024	14	30	420	=	420		108	126	126	60
4	Kees	15/01/2024	04/03/2024	7	20	140	=	140		52	84	4
5	Klaas1	08/10/2024	31/12/2024	12	25	300	=	300											90	105	105
6	Laura	16/01/2024	31/12/2024	50	32	1600	=	1600		76,8	134,4	134,4	140,8	147,2	128	147,2	140,8	134,4	147,2	134,4	134,4
7
8
9	2) 7 days a week
10	Name	Start contract	End contract	Contract weeks	Hours a week	Control		Control		ene-24	feb-24	mar-24	abr-24	may-24	jun-24	jul-24	ago-24	sep-24	oct-24	nov-24	dic-24
11	Piet	08/01/2024	15/04/2024	14	30	420	=	420		102,8571429	124,285714	132,857143	60
12	Kees	15/01/2024	04/03/2024	7	20	140	=	140		48,57142857	82,8571429	8,57142857
13	Klaas1	08/10/2024	31/12/2024	12	25	300	=	300											85,7142857	107,142857	107,142857
14	Laura	16/01/2024	31/12/2024	50	32	1600	=	1600		73,14285714	132,571429	141,714286	137,142857	141,714286	137,142857	141,714286	141,714286	137,142857	141,714286	137,142857	137,142857
15
16
17	3) 7 days a week (rounded by the hour) potencial error due to rounding
18	Name	Start contract	End contract	Contract weeks	Hours a week	Control		Control		ene-24	feb-24	mar-24	abr-24	may-24	jun-24	jul-24	ago-24	sep-24	oct-24	nov-24	dic-24
19	Piet	08/01/2024	15/04/2024	14	30	420	=	420		103	124	133	60
20	Kees	15/01/2024	04/03/2024	7	20	140	<>	141		49	83	9
21	Klaas1	08/10/2024	31/12/2024	12	25	300	=	300											86	107	107
22	Laura	16/01/2024	31/12/2024	50	32	1600	<>	1601		73	133	142	137	142	137	142	142	137	142	137	137
23
24
25	4) 7 days a week (rounded by the hour, corrected last month)
26	Name	Start contract	End contract	Contract weeks	Hours a week	Control		Control		ene-24	feb-24	mar-24	abr-24	may-24	jun-24	jul-24	ago-24	sep-24	oct-24	nov-24	dic-24
27	Piet	08/01/2024	15/04/2024	14	30	420	=	420		103	124	133	60
28	Kees	15/01/2024	04/03/2024	7	20	140	=	140		49	83	8
29	Klaas1	08/10/2024	31/12/2024	12	25	300	=	300											86	107	107
30	Laura	16/01/2024	31/12/2024	50	32	1600	=	1600		73	133	142	137	142	137	142	142	137	142	137	136
5 days a week

Cell Formulas
Range	Formula
F3:F6,F27:F30,F19:F22,F11:F14	F3	=D3*E3
G3:G6,G27:G30,G19:G22,G11:G14	G3	=IF(F3=H3," = ", " <> ")
H3:H6,H27:H30,H19:H22,H11:H14	H3	=SUM(J3:U3)
J3:U6	J3	=LET(d,NETWORKDAYS(MAX($B3,J$2),MIN($C3-1,EOMONTH(J$2,0)))*$E3/5, IF(d>=0,d,"") )
D3:D6,D27:D30,D19:D22,D11:D14	D3	=(C3-B3)/7
J11:U14	J11	=LET(d,NETWORKDAYS.INTL(MAX($B11,J$10),MIN($C11-1,EOMONTH(J$10,0)),"0000000")*$E11/7, IF(d>=0,d,"") )
J19:U22	J19	=LET(d,NETWORKDAYS.INTL(MAX($B19,J$18),MIN($C19-1,EOMONTH(J$18,0)),"0000000")*$E19/7, IF(d>=0,ROUND(d,0),"") )
J27:U30	J27	=LET(d,NETWORKDAYS.INTL(MAX($B27,J$26),MIN($C27-1,EOMONTH(J$26,0)),"0000000")*$E27/7, IF(d>=0,IF(MONTH($C27)=MONTH(J$26),$D27*$E27-SUM($I27:I27), ROUND(d,0)),"") )

 

[felixstraube]

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

Issue new-5.xlsx
	A	B	C	D	E	F	G	H		J	K	L	M	N	O	P	Q	R	S	T	U
33	5) 7 days a week by days and proportional of the week					Days		Days
34	Name	Start contract	End contract	Contract weeks	Hours a week	Control		Control		ene-24	feb-24	mar-24	abr-24	may-24	jun-24	jul-24	ago-24	sep-24	oct-24	nov-24	dic-24
35	Piet	08/01/2024	15/04/2024	14	30	98	=	98		24	29	31	14
36	Kees	15/01/2024	04/03/2024	7	20	49	=	49		17	29	3
37	Klaas1	08/10/2024	31/12/2024	12	25	84	=	84											24	30	30
38	Laura	16/01/2024	31/12/2024	50	32	350	=	350		16	29	31	30	31	30	31	31	30	31	30	30
39
40						Divided by 7 -->		14		3,428571429	4,14285714	4,42857143	2
41								7		2,428571429	4,14285714	0,42857143
42								12											3,42857143	4,28571429	4,28571429
43								50		2,285714286	4,14285714	4,42857143	4,28571429	4,42857143	4,28571429	4,42857143	4,42857143	4,28571429	4,42857143	4,28571429	4,28571429
44
45						Multiplied by # of hours per week -->		420		102,8571429	124,285714	132,857143	60
46								140		48,57142857	82,8571429	8,57142857
47								300											85,7142857	107,142857	107,142857
48								1600		73,14285714	132,571429	141,714286	137,142857	141,714286	137,142857	141,714286	141,714286	137,142857	141,714286	137,142857	137,142857
5 days a week

Cell Formulas
Range	Formula
F35:F38	F35	=D35*7
G35:G38	G35	=IF(F35=H35," = ", " <> ")
H35:H38,H45:H48,H40:H43	H35	=SUM(J35:U35)
J35:U38	J35	=LET(d,NETWORKDAYS.INTL(MAX($B35,J$34),MIN($C35-1,EOMONTH(J$34,0)),"0000000"), IF(d>=0,d,"") )
D35:D38	D35	=(C35-B35)/7
J40:U43	J40	=IF(ISNUMBER(J35),J35/7,"")
J45:U48	J45	=IF(ISNUMBER(J40),J40*$E35,"")

 

[Wishmaster40]

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!!!

 

[phiero21]

Thanks