Count the days in a 7-day period in a given month

[ThatExcelLife]

Hi Team!

I am banging my head against a wall on this excel issue. Please see below. What I have on the top row is the start of a new week (i.e. 30-Apr-2022 means the 7 day period that starts on 30-Apr-2022 and ends on 6-May-2022). what I am attempting to do is have a formula that will return 6 days in cell C2, as in there are 6 days in the 7 day period starting 30-Apr-2022 in the month of May (cell A2). in cell G2, the formula should return 4 days, as in there are 4 days in the month of May between 28-May-2022 and 6-June-2022. Any week that is complete in the month of May should be 7.

The idea is to drag the formula down to calculate the same for June, July, and so on.

It seems simple but I am crossing wires such that I cannot get to a perfect 31 days for May or 30 days for June.

Further, I do not want to use DATEDIF as a function since its not the standard function and when I share the sheet, it might crash on another's excel.

Any help would be appreciated!

	23-Apr-2022	30-Apr-2022	7-May-2022	14-May-2022	21-May-2022	28-May-2022	4-June-2022	11-June-2022
May 2022	0	6	7	7	7	4	0	0
June 2022	0	0	0	0	0		4	7

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		23-Apr-22	30-Apr-22	7-May-22	14-May-22	21-May-22	28-May-22	4-Jun-22	11-Jun-22	18-Jun-22	25-Jun-22	2-Jul-22	9-Jul-22	16-Jul-22	23-Jul-22	30-Jul-22
2	May-22	0	6	7	7	7	4	0	0	0	0	0	0	0	0	0
3	Jun-22	0	0	0	0	0	3	7	7	7	6	0	0	0	0	0
4	7/1/2022	0	0	0	0	0	0	0	0	0	1	7	7	7	7	2
5	8/1/2022	0	0	0	0	0	0	0	0	0	0	0	0	0	0	5
Sheet4

Cell Formulas
Range	Formula
B2:P5	B2	=MAX(0,MIN(EOMONTH($A2,0),B$1+6)-MAX($A2,B$1)+1)

 

[BSALV]

oei, almost the same as Eric W

Map1
	A	B	C	D	E	F	G	H	I	J
1			23/apr	30/apr	7/mei	14/mei	21/mei	28/mei	4/jun	11/jun
2
3	apr 22		7	1	0	0	0	0	0	0
4	mei 22		0	6	7	7	7	4	0	0
5	jun 22		0	0	0	0	0	3	7	7
6	jul 22		0	0	0	0	0	0	0	0
7	aug 22		0	0	0	0	0	0	0	0
8	sep 22
9	okt 22
10	nov 22
Blad1

Cell Formulas
Range	Formula
D1:J1	D1	=+C1+7
C3:J7	C3	=MAX(0,MIN(+C$1+6,EOMONTH($A3,0))-MAX(C$1,$A3)+1)
A4:A10	A4	=EDATE(A3,1)

 

[ThatExcelLife]

Try:

ERIC: I have spend over 2 HOURS trynna solve this from all sorts of angles and you destroy the problem in minutes! Wow I was making it way more complicated then it needed to be. HUGE thanks to you and your excel wizard abilities You saved me time and stress! Really appreciate your quick response too.

Cheers to Eric for helping me out in a big way!

 

[Eric W]

It helps that I've seen this problem before!

Glad we could help!