Calculate netdays based on hours per week

**Smythikal** · 09-01-2019, 11:17 AM

Hello all,

I am trying to calculate the rent of shipping containers based on hours per x workdays per week. See attached file and/or screenshot for 2 examples.

The first week of May 2019 have 3 workdays, I can bill these 3 workdays (contract allows me to bill max 4 days per week).
The other weeks in May have 5 workdays and I can bill max 4 workdays of each week.

If I use the formula to calculate the net days, without the weekends and holidays, it will not give me the exact days.
I thought by using the 'ceiling'. It will work for the first example, but not the second example.

Probably there is a simple formula for this, but not seeing it at the moment. I hope my problem is described well, if not, don't hesitate to ask if you have any questions. Any help is much appreciated

example.jpg

**Bo_Ry** · 09-01-2019, 11:48 AM

Please try at
C13 Assume that Start and End not in the same week
=SUM(MIN(4,6-WEEKDAY(C7,2)),MIN(4,WEEKDAY(C8,2)),4*(SUMPRODUCT(WEEKNUM(+C7:C8),{-1;1})-1))

or if Start and End could be in the same week
C13
=IFERROR(SUM(MIN(4,6-WEEKDAY(C7,2)),MIN(4,WEEKDAY(C8,2)),4*1/(1/(SUMPRODUCT(WEEKNUM(+C7:C8),{-1;1}))-1)),MIN(4,NETWORKDAYS(C7,C8)))

**Richard Buttrey** · 09-01-2019, 12:07 PM

Does the following in C13 help, and then copied to C29...etc

Formula:

Please Login or Register  to view this content.

The idea is to identify the minimum of 4 and the working days in the first week, and the same for the last week (Using the WEEKDAY() function).
Then subtract the total of the working days in the first and last week from the working days in the whole month - this will always be a multiple of 5, and divide this by 5 and multipy by 4.

**Smythikal** · 09-01-2019, 12:38 PM

Hey Bo_Ry & Richard Buttrey, thank you guys very much for the fast reply, mucho appreciation from me!
I will check it out, testing now

**Smythikal** · 09-01-2019, 02:14 PM

I have tested both formula's and it's working great for these examples. Except if I want to change the start-/enddate (like a date in middle of month) and the number of workdays.

The following fields can have different input: Max hours per week, Max days per week, max hours per day, price per hour, startdate and enddate.
Max days can be between 1 and 5 days, the price and hours per day can be different and the startdate/enddate can also be just a date in the middle of the month.
I am looking for a formula that can handle these different inputs and can calculate it based on the inputs, without me looking at a calendar

example.jpg

**Bo_Ry** · 09-01-2019, 02:56 PM

How about
C13
=SUM(MIN(C40,7-WEEKDAY(C43)),MIN(C40,WEEKDAY(C44)-1),C40*(WEEKNUM(C44)-WEEKNUM(C43)-1))

**Richard Buttrey** · 09-01-2019, 03:11 PM

Does

Formula:

Please Login or Register  to view this content.

work for you?

**Smythikal** · 09-02-2019, 01:40 PM

Hi guys, sorry for my late reply, was a busy day.

I have tried both of them and the formula from Bo_Ry is getting close. It only works with dates of the same year. For example if I use as startdate 05-05-2019 and enddate 10-16-2020, the formula will not work. I have tried to add multiple functions to add up the workdays of the other year, but it's getting messy.

The formula with roundup function from Richard Buttrey doesn't work, most of the times I am always 1 day short.

**Bo_Ry** · 09-02-2019, 01:52 PM

Please try at C13
=SUM(MIN(C4,7-WEEKDAY(C7)),MIN(C4,WEEKDAY(C8)-1),C4*(C8-WEEKDAY(C8)-C7+WEEKDAY(C7)-7)/7)

**Smythikal** · 09-02-2019, 02:10 PM

Many thanks Richard and Bo_Ry for your help, the last formula from Bo_Ry works like a charm

Calculate netdays based on hours per week

LinkBack

Thread Tools

Rate This Thread

Display

Calculate netdays based on hours per week

Re: Calculate netdays based on hours per week

Re: Calculate netdays based on hours per week

Re: Calculate netdays based on hours per week

Re: Calculate netdays based on hours per week

Re: Calculate netdays based on hours per week

Re: Calculate netdays based on hours per week

Re: Calculate netdays based on hours per week

Re: Calculate netdays based on hours per week

Re: Calculate netdays based on hours per week

Thread Information

Users Browsing this Thread

Similar Threads

How to calculate hours, week by week...

[SOLVED] Formula to Calculate Normal Hours, Time & 1/2 & Double Time from Daily Hours per week

Calculate hours between two times per day and add them up, result at the end of the week.

[SOLVED] 40 Hours per week / how to calculate overtime hours

Formula to Calculate Hours over Week Range

Rota - calculate the hours every one has done each week

Calculate Hours and overtime by week

[SOLVED] How do I calculate the week day hours between 2 days

Bookmarks

Bookmarks

Posting Permissions