Dates Help! Using SUMPRODUCT,MONTH<ROW< INDIRECT and network days

[sgibbs183]

I have a sheet below where I need to calculate how many days between the start and end date fall into that period. I have done this, so for example, 30 days falls into April from start date 28/03/2022 to end date 01/05/2022. But I want to know how many of these days are NETWORKDAYS so I can times this amount by the blended rate.

Is there anyone that can help please!

 

[jorismoerings]

Hi,

Tried to make it as easy as possible. Try this and see if this helps:

Book1
	D	E	F	G	H
2	Start	end	1-3-2022	1-4-2022	1-5-2022
3	28-3-2022	1-5-2022	4	21	0
4	28-3-2022	1-5-2022	3	29	0
Sheet1

Cell Formulas
Range	Formula
F3:H3	F3	=NETWORKDAYS(MAX($D3,F$2),MIN($E3,EOMONTH(F$2,0)))
F4:H4	F4	=DATEDIF(MAX($D4,F$2),MIN($E4,EOMONTH(F$2,0)),"d")

Just be aware: the NETWORKDAYS function calculates somewhat different so perhaps for your purpose you need to add a day extra to the equasion.

 

[shinigamilight]

Try using this

=NETWORKDAYS(D4,E4)

 

[Fluff]

What exactly do you have in G3, H3 etc? Is it 1st of the month, end of the month or something else.

 

[sgibbs183]

What exactly do you have in G3, H3 etc? Is it 1st of the month, end of the month or something else.

It was just the month in text,

But what I actually need is how many network days between the start and end date, fall into that period. I have added the period start and end dates to the table to see if that helps but I still can't get it to work.

 

[sgibbs183]

Hi,

Tried to make it as easy as possible. Try this and see if this helps:

Book1
	D	E	F	G	H
2	Start	end	1-3-2022	1-4-2022	1-5-2022
3	28-3-2022	1-5-2022	4	21	0
4	28-3-2022	1-5-2022	3	29	0
Sheet1

Cell Formulas
Range	Formula
F3:H3	F3	=NETWORKDAYS(MAX($D3,F$2),MIN($E3,EOMONTH(F$2,0)))
F4:H4	F4	=DATEDIF(MAX($D4,F$2),MIN($E4,EOMONTH(F$2,0)),"d")

Just be aware: the NETWORKDAYS function calculates somewhat different so perhaps for your purpose you need to add a day extra to the equasion.

Sorry I have added the start date and end date of the period end dates.

What I need to know is how many network days from start date and end date fall into the period end dates...

 

[Fluff]

Thanks for that, how about

=NETWORKDAYS(MAX($D6,G$3),MIN($E6,G$4,0))

 

[sgibbs183]

Thanks for that, how about

=NETWORKDAYS(MAX($D6,G$3),MIN($E6,G$4,0))

That returns an odd number:

But I did change this to median and it works sort off, but as you can see in the later months it is calculating the days from the start date

 

[Fluff]

A stray 0 crept into the formula it should be

=NETWORKDAYS(MAX($D6,G$3),MIN($E6,G$4))

 

[sgibbs183]

A stray 0 crept into the formula it should be

=NETWORKDAYS(MAX($D6,G$3),MIN($E6,G$4))

Thanks Fluff, that doesn't work either. If you see below, the start date is 28/03/22 end date 10/05/2022, the network days in May should be from 2nd may to 10th May but it is returning 20