Calculating no. of working days including saturdays

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,701
Office Version
  1. 2019
Platform
  1. Windows
I have the following formula in A2 ="Parts Man Report "&TEXT(NOW(),"dd/mm/yyyy")&""

I would like to set up a formula to computte the number of working days in the month including saturday and exluding public holidays (I would set up a table for the public holidays)
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Which version of Excel do you have?

And do you mean the Current Month? or some other month otherwise specified?
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,701
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the reply. Excel 2007. I mean the current month
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try this

=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,G1-DAY(G1)+1):INDEX(A:A,EOMONTH(G1,0))))<>1),--(ISNA(MATCH(ROW(INDEX(A:A,G1-DAY(G1)+1):INDEX(A:A,EOMONTH(G1,0))),F1:F10,0))))

G1: =TODAY()
F1:F10 = your list of holiday dates
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,701
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Thanks for the help, much appreciated
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,701
Office Version
  1. 2019
Platform
  1. Windows
How do I amend formula to only include weekdays (public holidays stiill to be excluded)?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

How do I amend formula to only include weekdays (public holidays stiill to be excluded)?
So this meaning exclude Saturday AND Sunday?

=NETWORKDAYS(G1-DAY(G1)+1,EOMONTH(G1,0),F1:F10)

G1: =TODAY()
F1:F10 = your list of holiday dates
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,701
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the help, much appreciated

How does the EOMONTh formula work?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Glad to help, thanks for the feedback..

It finds the last day of the month that is the given # of months from a given date.
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,701
Office Version
  1. 2019
Platform
  1. Windows
Thanks, I thought this may be the case, but was not 100% certain

I've learnt so much from the help, I have got on this site, more than from any Excel text book
 

Watch MrExcel Video

Forum statistics

Threads
1,123,257
Messages
5,600,560
Members
414,388
Latest member
Pkmep4

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