Help with formula to calculate number of days in a month between days

[jasedc5r]

Hi All,

I need some assistance with a formula (Cell D2) which can calculate the number of days in a month (excluding weekends and holidays tab) between two dates and in the same formula which can calculate the per day penalty rate (i.e. $500 per day). Cell to spit out the total penalty cost.

Please see table below.

Thanks in advance for your help!

 

[Peter_SSs]

In future it would help if you could give your sample data (& expected results**) with so that we can easily copy for testing instead of manually typing data.

** Interesting sample data you gave as the results for that sample data would be nothing.

See if something like this would help.

jasedc5r.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18				Holidays
2	2/07/2018	21/09/2018					10,000	10,500	7,500					15/07/2018
3														16/07/2018
4														17/07/2018
5														8/08/2018
6														9/08/2018
7
Sheet1

Cell Formulas
Range	Formula
D2:K2	D2	=IF(MEDIAN($B2-DAY($B2)+1,$C2,D$1)=D$1,NETWORKDAYS(D1,MIN(EOMONTH(D$1,0),$C2),$O$2:$O$10)*500,"")

 

[jasedc5r]

Thanks Peter. I note your feedback and will apply in future posts.

 

[Peter_SSs]

Thanks Peter.

You're welcome. Thanks for the follow-up.

 

[jasedc5r]

Hi Peter,

The formula works great, however, the total penalty rate is incorrect in some cells. Please refer to cell R3 below which should be "-3,000" (6 working days).

I note that the formula seems to be working fine in the table you provided above.

Book3
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2	Actual Start Date	Actual Finish Date	EOT	Duration	Contract Finish Date	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20
3	9-Feb-20	22-Jun-20	0	60	10-Jul-20												10500	4000
Sheet4

Cell Formulas
Range	Formula
G3:U3	G3	=IF(MEDIAN($C3-DAY($C3)+1,$F3,G$2)=G$2,NETWORKDAYS(G2,MIN(EOMONTH(G$2,0),$F3),$AI$6:$AI$110)*500,"")

 

[Peter_SSs]

Could you give us 3 or 4 small but varied examples (XL2BB) with the expected results (& relevant holidays) included and explain in words how you get a few of those expected results?

 

[jasedc5r]

Book3
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2	Actual Start Date	Actual Finish Date	EOT	Duration	Contract Finish Date	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20
3	9-Feb-20	22-Jun-20	0	60	10-Jul-20												10500	4000
4	21-Apr-20	23-Jun-20	0	60	14-Jul-20													14273000
5	1-May-20	13-Jul-20	0	45	8-Jul-20													#NUM!
Sheet4

Cell Formulas
Range	Formula
G3:U3,S4:U4,G4:Q5,R5:U5	G3	=IF(MEDIAN($C3-DAY($C3)+1,$F3,G$2)=G$2,NETWORKDAYS(G2,MIN(EOMONTH(G$2,0),$F3),$AI$6:$AI$110)*500,"")
R4	R4	=IF(MEDIAN($F4-DAY($F4)+1,$C4,R$2)=R$2,NETWORKDAYS(R3,MIN(EOMONTH(R$2,0),$C4),$AI$6:$AI$110)*500,"")

Book3
	AI
1
2
3	Victorian Public Holidays
4
5	Date
6	1/01/2018
7	26/01/2018
8	12/03/2018
9	30/03/2018
10	2/04/2018
11	25/04/2018
12	11/06/2018
13	28/09/2018
14	6/11/2018
15	25/12/2018
16	26/12/2018
17	27/12/2018
18	28/12/2018
19	31/12/2018
20	1/01/2019
21	2/01/2019
22	3/01/2019
23	4/01/2019
24	28/01/2019
25	11/03/2019
26	19/04/2019
27	22/04/2019
28	25/04/2019
29	10/06/2019
30	27/09/2019
31	5/11/2019
32	23/12/2019
33	24/12/2019
34	25/12/2019
35	26/12/2019
36	27/12/2019
37	30/12/2019
38	31/12/2019
39	1/01/2020
40	2/01/2020
41	3/01/2020
42	27/01/2020
43	9/03/2020
44	10/04/2020
45	13/04/2020
46	14/04/2020
47	15/04/2020
48	16/04/2020
49	17/04/2020
50	8/06/2020
51	23/10/2020
52	3/11/2020
53	21/12/2020
54	22/12/2020
55	23/12/2020
56	24/12/2020
57	25/12/2020
58	28/12/2020
59	29/12/2020
60	30/12/2020
61	31/12/2020
62	1/01/2021
63	26/01/2021
64	8/03/2021
65	2/04/2021
66	5/04/2021
67	14/06/2021
68	24/09/2021
69	2/11/2021
70	27/12/2021
71	28/12/2021
72	29/12/2021
73	30/12/2021
74	31/12/2021
75	3/01/2022
76	4/01/2022
77	5/01/2022
78	6/01/2022
79	7/01/2022
80	26/01/2022
81	14/03/2022
82	15/04/2022
83	18/04/2022
84	25/04/2022
85	13/06/2022
86	30/09/2022
87	1/11/2022
88	26/12/2022
89	27/12/2022
90	28/12/2022
91	29/12/2022
92	30/12/2022
93	2/01/2023
94	3/01/2023
95	4/01/2023
96	5/01/2023
97	6/01/2023
98	26/01/2023
99	13/03/2023
100	7/04/2023
101	10/04/2023
102	25/04/2023
103	12/06/2023
104	29/09/2023
105	2/11/2023
106	25/12/2023
107	26/12/2023
108	27/12/2023
109	28/12/2023
110	29/12/2023
Sheet4

Expected results
Cell R3 = -3,000 (6 working days in the month*500)
Cell S3 = -4,000 (8 working days in the month*500)
Cell R4 = -2,500 (5 working days in the month*500)
Cell S4 = -5,000 (10 working days in the month*500)
Cell S5 = 1,500 (10 working days in the month*500)

 

[Peter_SSs]

Thanks for the extra info. There is probably a shorter way than this, but I think it produces the results you want?
Same holiday list as you. (Dates are only formatted as I have to help with manual checking)

jasedc5r.xlsm
	C			F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2	Actual Finish Date			Contract Finish Date	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20
3	Monday, 22 June 2020			Friday, 10 July 2020												-3000	-4000
4	Tuesday, 23 June 2020			Tuesday, 14 July 2020												-2500	-5000
5	Monday, 13 July 2020			Wednesday, 8 July 2020													1500
6	Thursday, 13 August 2020			Friday, 5 June 2020												8000	11500	4500
7	Wednesday, 18 December 2019			Monday, 6 January 2020						-1000	-500
Sheet3

Cell Formulas
Range	Formula
G3:U7	G3	=LET(s,SEQUENCE(MAX($C3,$F3)-MIN($C3,$F3),,MIN($C3,$F3)+1),f,FILTER(s,(TEXT(s,"mmyy")=TEXT(G$2,"mmyy"))*(WEEKDAY(s,2)<6)*ISNA(MATCH(s,$AI:$AI,0)),""),k,COUNT(f),IF(k,k*500*IF($C3>$F3,1,-1),""))

 

[jasedc5r]

Thanks Peter for all your help on this. The new formula works a treat!

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.