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

jasedc5r

New Member
Joined
Apr 26, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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.

1648788479198.png


Thanks in advance for your help!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,482
Office Version
  1. 365
Platform
  1. Windows
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
BCDEFGHIJKLMNO
1Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Holidays
22/07/201821/09/2018    10,00010,5007,500 15/07/2018
316/07/2018
417/07/2018
58/08/2018
69/08/2018
7
Sheet1
Cell Formulas
RangeFormula
D2:K2D2=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

New Member
Joined
Apr 26, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Thanks Peter. I note your feedback and will apply in future posts.
 

jasedc5r

New Member
Joined
Apr 26, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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
BCDEFGHIJKLMNOPQRSTU
2Actual Start DateActual Finish DateEOTDurationContract Finish DateJul-19Aug-19Sep-19Oct-19Nov-19Dec-19Jan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20
39-Feb-2022-Jun-2006010-Jul-20           105004000  
Sheet4
Cell Formulas
RangeFormula
G3:U3G3=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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,482
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 26, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Book3
BCDEFGHIJKLMNOPQRSTU
2Actual Start DateActual Finish DateEOTDurationContract Finish DateJul-19Aug-19Sep-19Oct-19Nov-19Dec-19Jan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20
39-Feb-2022-Jun-2006010-Jul-20           105004000  
421-Apr-2023-Jun-2006014-Jul-20            14273000  
51-May-2013-Jul-200458-Jul-20            #NUM!  
Sheet4
Cell Formulas
RangeFormula
G3:U3,S4:U4,G4:Q5,R5:U5G3=IF(MEDIAN($C3-DAY($C3)+1,$F3,G$2)=G$2,NETWORKDAYS(G2,MIN(EOMONTH(G$2,0),$F3),$AI$6:$AI$110)*500,"")
R4R4=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
3Victorian Public Holidays
4
5Date
61/01/2018
726/01/2018
812/03/2018
930/03/2018
102/04/2018
1125/04/2018
1211/06/2018
1328/09/2018
146/11/2018
1525/12/2018
1626/12/2018
1727/12/2018
1828/12/2018
1931/12/2018
201/01/2019
212/01/2019
223/01/2019
234/01/2019
2428/01/2019
2511/03/2019
2619/04/2019
2722/04/2019
2825/04/2019
2910/06/2019
3027/09/2019
315/11/2019
3223/12/2019
3324/12/2019
3425/12/2019
3526/12/2019
3627/12/2019
3730/12/2019
3831/12/2019
391/01/2020
402/01/2020
413/01/2020
4227/01/2020
439/03/2020
4410/04/2020
4513/04/2020
4614/04/2020
4715/04/2020
4816/04/2020
4917/04/2020
508/06/2020
5123/10/2020
523/11/2020
5321/12/2020
5422/12/2020
5523/12/2020
5624/12/2020
5725/12/2020
5828/12/2020
5929/12/2020
6030/12/2020
6131/12/2020
621/01/2021
6326/01/2021
648/03/2021
652/04/2021
665/04/2021
6714/06/2021
6824/09/2021
692/11/2021
7027/12/2021
7128/12/2021
7229/12/2021
7330/12/2021
7431/12/2021
753/01/2022
764/01/2022
775/01/2022
786/01/2022
797/01/2022
8026/01/2022
8114/03/2022
8215/04/2022
8318/04/2022
8425/04/2022
8513/06/2022
8630/09/2022
871/11/2022
8826/12/2022
8927/12/2022
9028/12/2022
9129/12/2022
9230/12/2022
932/01/2023
943/01/2023
954/01/2023
965/01/2023
976/01/2023
9826/01/2023
9913/03/2023
1007/04/2023
10110/04/2023
10225/04/2023
10312/06/2023
10429/09/2023
1052/11/2023
10625/12/2023
10726/12/2023
10827/12/2023
10928/12/2023
11029/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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,482
Office Version
  1. 365
Platform
  1. Windows
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
CFGHIJKLMNOPQRSTU
2Actual Finish DateContract Finish DateJul-19Aug-19Sep-19Oct-19Nov-19Dec-19Jan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20
3Monday, 22 June 2020Friday, 10 July 2020           -3000-4000  
4Tuesday, 23 June 2020Tuesday, 14 July 2020           -2500-5000  
5Monday, 13 July 2020Wednesday, 8 July 2020            1500  
6Thursday, 13 August 2020Friday, 5 June 2020           8000115004500 
7Wednesday, 18 December 2019Monday, 6 January 2020     -1000-500        
Sheet3
Cell Formulas
RangeFormula
G3:U7G3=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

New Member
Joined
Apr 26, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Thanks Peter for all your help on this. The new formula works a treat!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,482
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the confirmation. :)
 

Forum statistics

Threads
1,175,531
Messages
5,897,955
Members
434,688
Latest member
vi28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top