Excel formula to list all dates of a month excluding weekends and holidays.

abhay_547

Board Regular
Joined
Sep 12, 2009
Messages
179
Hi All,

I want to built a formula which will list all working days in a month excluding weekends .i.e. saturdays and sundays and it will also exclude the list of the dates present in a different column .i.e. the holidays in that month and give us a list of working days. I have the below formula which will list down the dates excluding the weekends in a month if I put the first working day over it. for e.g. if my date is in cell A1 and cell A2 I enter the below formula and drag it down then it will give me the list of all working days.

=IF(WEEKDAY(A1+1,2)<6,A1+1,IF(WEEKDAY(A1,2)=6,A1+2,A1+3))

I don't want to use the Networkdays as for that I will have to select analysis tool addin in the excel and if i send the same file to some other person and if the above addin is not selected in his excel then he will get #name error. So I want to build a formula without using networkdays from analysis tool.

Thanks a lot for your help in advance.:)
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
If you have the firts working day of the month in A1 you can use this formula in A2 copied down

=MIN(IF(WEEKDAY(A1+{1,2,3,4,5},2)<6,IF(ISNA(MATCH(A1+{1,2,3,4,5},H$1:H$10,0)),{1,2,3,4,5})))+A1

where H1:H10 contains a list of holiday dates

Assumes that you never have more than 4 successive non-working days......
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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
Back
Top