Count of days between two dates, but within a given period

[Excellerator365]

Hi,

For payroll purposes I need to be able to calculate the amount of days between two dates, but within a given payroll period.
I need this in a formula that I can drag accross all my data (alot of it)

Any help is greatly apprechiated.

 

[DRSteele]

Welcome to the forum.

I think this does it. There are several nested IF statements, which is amateurish, but I couldn't reckon another way on such short notice.

[Please download the MrExcel Message Board App so you can upload data and save us having to type it in. See my signature below for the link.]

MrExcel posts19.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3			begin:	1/1/2022	2/1/2022	3/1/2022	4/1/2022	5/1/2022	6/1/2022	7/1/2022	8/1/2022	9/1/2022	10/1/2022	11/1/2022	12/1/2022
4			end:	1/31/2022	2/28/2022	3/31/2022	4/30/2022	5/31/2022	6/30/2022	7/31/2022	8/31/2022	9/30/2022	10/31/2022	11/30/2022	12/31/2022
5
6	start	finish
7	12/15/2021	6/15/2022		31	28	31	30	31	15	0	0	0	0	0	0
8	5/5/2022	2/15/2023		0	0	0	0	27	30	31	31	30	31	30	31
9	1/10/2022	10/20/2022		22	28	31	30	31	30	31	31	30	20	0	0
Sheet32

Cell Formulas
Range	Formula
E7:P9	E7	=IF(($B7<E$3)*($C7>E$4),E$4-E$3+1,IF(($C7<E$4)*($C7>=E$3),$C7-E$3+1,IF(($B7>=E$3)*($B7<=E$4),E$4-$B7+1,0)))

 

[jasonb75]

Is it days (as mentioned in the question and thread title) or days worked (as mentioned in the screen capture)?
This is based on a 7 day working week with no holidays. You can amend the weekend and holiday parameters of the NETWORKDAYS.INTL function as required.

Mr Excel 14-11-22.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			Start	01/01/2022	01/02/2022	01/03/2022	01/04/2022	01/05/2022	01/06/2022	01/07/2022	01/08/2022	01/09/2022	01/10/2022	01/11/2022	01/12/2022
2			End	31/01/2022	28/02/2022	31/03/2022	30/04/2022	31/05/2022	30/06/2022	31/07/2022	31/08/2022	30/09/2022	31/10/2022	30/11/2022	31/12/2022
3
4				Days
5	Start	End
6	15/12/2021	15/03/2022		31	28	15	0	0	0	0	0	0	0	0	0
7	05/05/2022	15/02/2023		0	0	0	0	27	30	31	31	30	31	30	31
8	10/01/2022	20/10/2022		22	28	31	30	31	30	31	31	30	20	0	0
Sheet10

Cell Formulas
Range	Formula
D6:O8	D6	=MAX(0,NETWORKDAYS.INTL(MAX($A6,D$1),MIN($B6,D$2),"0000000"))

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Calculate Working Days Between Two Dates
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.