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

Wishmaster40

New Member
Joined
Dec 18, 2023
Messages
12
Office Version
  1. 2021
Platform
  1. Windows
  2. MacOS
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!
 

Attachments

  • Screen Shot 2023-12-18 at 15.50.29.png
    Screen Shot 2023-12-18 at 15.50.29.png
    56.3 KB · Views: 21
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:

MonTueWedThuFriSatSunTotal
555555030

or for 20:

MonTueWedThuFriSatSunTotal
444421120

or for 25 something like:

MonTueWedThuFriSatSunTotal
554443025

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?
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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/202301/01/202402/01/202403/01/202404/01/202405/01/202406/01/2024
07/01/202408/01/202409/01/202410/01/202411/01/202412/01/202413/01/2024
14/01/202415/01/202416/01/202417/01/202418/01/202419/01/202420/01/2024
21/01/202422/01/202423/01/202424/01/202425/01/202426/01/202427/01/2024
28/01/202429/01/202430/01/202431/01/202401/02/202402/02/202403/02/2024
04/02/202405/02/202406/02/202407/02/202408/02/202409/02/202410/02/2024
11/02/202412/02/202413/02/202414/02/202415/02/202416/02/202417/02/2024
18/02/202419/02/202420/02/202421/02/202422/02/202423/02/202424/02/2024
25/02/202426/02/202427/02/202428/02/202429/02/202401/03/202402/03/2024
03/03/202404/03/202405/03/202406/03/202407/03/202408/03/202409/03/2024
10/03/202411/03/202412/03/202413/03/202414/03/202415/03/202416/03/2024
17/03/202418/03/202419/03/202420/03/202421/03/202422/03/202423/03/2024
24/03/202425/03/202426/03/202427/03/202428/03/202429/03/202430/03/2024
31/03/202401/04/202402/04/202403/04/202404/04/202405/04/202406/04/2024
07/04/202408/04/202409/04/202410/04/202411/04/202412/04/202413/04/2024
14/04/202415/04/202416/04/202417/04/202418/04/202419/04/202420/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
ABCDEFGHIJKLMNOPQRST
1NameStart contractEnd contractContract weeksHours a weekControlControlene-24feb-24mar-24abr-24may-24jun-24jul-24ago-24sep-24oct-24nov-24dic-24
2Piet08/01/202415/04/20241430420 = 42010812612660        
3Kees15/01/202404/03/2024720140 = 14052844         
4Klaas108/10/202431/12/20241225300 = 300         90105105
5Laura16/01/202431/12/202450321600 = 160076,8134,4134,4140,8147,2128147,2140,8134,4147,2134,4134,4
Sheet1 (2)
Cell Formulas
RangeFormula
F2:F5F2=D2*E2
G2:G5G2=IF(F2=H2," = ", " <> ")
H2:H5H2=SUM(I2:T2)
I2:T5I2=LET(d,NETWORKDAYS(MAX($B2,I$1),MIN($C2-1,EOMONTH(I$1,0)))*$E2/5, IF(d>=0,d,"") )
D2:D5D2=(C2-B2)/7
 
Upvote 0
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:
1702988554160.png


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
1702988722593.png


Again I really appreciate everything you are doing for me!!! Without your help I wouldn't be able to solve this issue for sure!!!
 
Upvote 0
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
ABCDEFGHJKLMNOPQRSTU
11) 5 days a week
2NameStart contractEnd contractContract weeksHours a weekControlControlene-24feb-24mar-24abr-24may-24jun-24jul-24ago-24sep-24oct-24nov-24dic-24
3Piet08/01/202415/04/20241430420 = 42010812612660        
4Kees15/01/202404/03/2024720140 = 14052844         
5Klaas108/10/202431/12/20241225300 = 300         90105105
6Laura16/01/202431/12/202450321600 = 160076,8134,4134,4140,8147,2128147,2140,8134,4147,2134,4134,4
7
8
92) 7 days a week
10NameStart contractEnd contractContract weeksHours a weekControlControlene-24feb-24mar-24abr-24may-24jun-24jul-24ago-24sep-24oct-24nov-24dic-24
11Piet08/01/202415/04/20241430420 = 420102,8571429124,285714132,85714360        
12Kees15/01/202404/03/2024720140 = 14048,5714285782,85714298,57142857         
13Klaas108/10/202431/12/20241225300 = 300         85,7142857107,142857107,142857
14Laura16/01/202431/12/202450321600 = 160073,14285714132,571429141,714286137,142857141,714286137,142857141,714286141,714286137,142857141,714286137,142857137,142857
15
16
173) 7 days a week (rounded by the hour) potencial error due to rounding
18NameStart contractEnd contractContract weeksHours a weekControlControlene-24feb-24mar-24abr-24may-24jun-24jul-24ago-24sep-24oct-24nov-24dic-24
19Piet08/01/202415/04/20241430420 = 42010312413360        
20Kees15/01/202404/03/2024720140 <> 14149839         
21Klaas108/10/202431/12/20241225300 = 300         86107107
22Laura16/01/202431/12/202450321600 <> 160173133142137142137142142137142137137
23
24
254) 7 days a week (rounded by the hour, corrected last month)
26NameStart contractEnd contractContract weeksHours a weekControlControlene-24feb-24mar-24abr-24may-24jun-24jul-24ago-24sep-24oct-24nov-24dic-24
27Piet08/01/202415/04/20241430420 = 42010312413360        
28Kees15/01/202404/03/2024720140 = 14049838         
29Klaas108/10/202431/12/20241225300 = 300         86107107
30Laura16/01/202431/12/202450321600 = 160073133142137142137142142137142137136
5 days a week
Cell Formulas
RangeFormula
F3:F6,F27:F30,F19:F22,F11:F14F3=D3*E3
G3:G6,G27:G30,G19:G22,G11:G14G3=IF(F3=H3," = ", " <> ")
H3:H6,H27:H30,H19:H22,H11:H14H3=SUM(J3:U3)
J3:U6J3=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:D14D3=(C3-B3)/7
J11:U14J11=LET(d,NETWORKDAYS.INTL(MAX($B11,J$10),MIN($C11-1,EOMONTH(J$10,0)),"0000000")*$E11/7, IF(d>=0,d,"") )
J19:U22J19=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:U30J27=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)),"") )
 
Upvote 0
Solution
Counting days as you said:
It is basically the same as option number 2)

Cell Formulas
RangeFormula
F35:F38F35=D35*7
G35:G38G35=IF(F35=H35," = ", " <> ")
H35:H38,H45:H48,H40:H43H35=SUM(J35:U35)
J35:U38J35=LET(d,NETWORKDAYS.INTL(MAX($B35,J$34),MIN($C35-1,EOMONTH(J$34,0)),"0000000"), IF(d>=0,d,"") )
D35:D38D35=(C35-B35)/7
J40:U43J40=IF(ISNUMBER(J35),J35/7,"")
J45:U48J45=IF(ISNUMBER(J40),J40*$E35,"")
 
Upvote 0
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!!!
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top