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

W

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

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
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?
 
Upvote 0
felixstraube said:
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?
Click to expand...
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
 
Upvote 0
How about?:

Validate month.xlsx
ABCDEFGHIJKLMNOPQR
1NameStartEndweeksHours a weekene-24feb-24mar-24abr-24may-24jun-24jul-24ago-24sep-24oct-24nov-24dic-24
2Piet01/01/202415/04/2024153013812612666        
3Kees15/01/202404/03/202472052848         
4Jan08/10/202431/12/20241225         90105110
Hoja1
Cell Formulas
RangeFormula
G2:R4G2=LET(d,NETWORKDAYS(MAX($B2,G$1),MIN($C2,EOMONTH(G$1,0)))*$E2/5, IF(d>=0,d,"") )
 
Upvote 0
felixstraube said:
How about?:

Validate month.xlsx
ABCDEFGHIJKLMNOPQR
1NameStartEndweeksHours a weekene-24feb-24mar-24abr-24may-24jun-24jul-24ago-24sep-24oct-24nov-24dic-24
2Piet01/01/202415/04/2024153013812612666        
3Kees15/01/202404/03/202472052848         
4Jan08/10/202431/12/20241225         90105110
Hoja1
Cell Formulas
RangeFormula
G2:R4G2=LET(d,NETWORKDAYS(MAX($B2,G$1),MIN($C2,EOMONTH(G$1,0)))*$E2/5, IF(d>=0,d,"") )
Click to expand...
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.
 
Upvote 0
felixstraube said:
Why would that not work?

I modified your file and uploaded it here:

Issue new-5 (edited Felix).zip
Click to expand...
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?
 
Upvote 0
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?
 
Upvote 0
felixstraube said:
so how many hour are you working each day?
Click to expand...

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.

NameStart contractEnd contractContract weeksHours a week
Piet08/01/202415/04/20241430
Kees15/01/202404/03/2024720
Klaas108/10/202431/12/20241225
Laura16/01/202431/12/20245032


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?

MonthMonth/yearStart monthNumber of weeks per month
1Jan/2401/01/20245
2Feb/2401/02/20244
3Mar/2401/03/20244
4Apr/2401/04/20245
5May/2401/05/20244
6Jun/2401/06/20244
7Jul/2401/07/20245
8Aug/2401/08/20244
9Sep/2401/09/20244
10Oct/2401/10/20245
11Nov/2401/11/20244
12Dec/2401/12/20244
52

So like this but then correct:
NameStart contractEnd contractContract weeksHours a weekJan/24Feb/24Mar/24Apr/24May/24Jun/24Jul/24Aug/24Sep/24Oct/24Nov/24Dec/24
Piet08/01/202415/04/2024143023544500000000
Kees15/01/202404/03/202472029544000000000
Klaas108/10/202431/12/2024122531000000000544
Laura16/01/202431/12/2024503215544544544544

Because now for Piet it writes 5 weeks for Jan/24 but this is not correct and the same for the other cells
 
Upvote 0
I don't see the point in counting weeks. Why you ask? Here is a calendar for 2024:

calendar.png


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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Weekly dates with split across weeks with two months
Replies
1
Views
211
Peter_SSs
Peter_SSs
A
  • Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
89
myall_blues
myall_blues
U
  • Question
Week number based on the year starting with a particular date
Replies
2
Views
489
Jeffrey Mahoney
Jeffrey Mahoney
F
  • Question
Sumifs for week of month
Replies
4
Views
119
awoohaw
awoohaw
J
  • Solved
Converting Month to Weeks from the current date
Replies
8
Views
336
jajatidev
J

Forum statistics

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

Share this page

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