Formula to calculate weekdays in multiple months

cdmaningo

New Member
Joined
Aug 2, 2010
Messages
3
I am trying to list working days in a sheet, horizontally for a year as below:

M T W T F M T
6/28 6/29 6/30 7/1 7/2 7/5 7/6 ......

Do you know a formula that I can use for this? I am trying to use a version of the formula below, however, I can only get it to work for 1 month.


Thank you!

Christina


Quote:
Originally Posted by Joe4
This is somewhat similar to IML, but it is a little different, so I will post it anyway.

Enter the first day of the month in A1, the in B1 enter this formula and copy down 23 times:
=IF(MONTH(WORKDAY($A$1-1,ROW()))=MONTH($A$1),WORKDAY($A$1-1,ROW()),"")

Note that I am making use of ROW(), which returns the current row number. It will also automatcially take care of the situation in which there are less than 23 workdays in a month.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,871
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
are you really sure you need a formula for this?
you may simply use the FillWeekdays functionality of Excel.
put in cell A1 the starting day that you need.
then select cell A1. grab the small black square at the bottom right of the selection rectangle but grab it with the RIGHT mouse button. While holding it - drag it across the worksheet as long as you like. When you release it a menu should appear -> select Fill Weekdays.
that's all there is to it if i got it right.
:)
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,349
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Use Excel's built in Fill dates

Excel 2003

Put a start date in a cell
Select a range of cells
Edit | Fill | Series | Weekdays
 

cdmaningo

New Member
Joined
Aug 2, 2010
Messages
3
Thank you to both for such a quick response. This was too easy. Sorry, not so well versed on excel. I was going insane trying to figure this out.

Christina
 

Forum statistics

Threads
1,176,344
Messages
5,902,597
Members
434,981
Latest member
sheils

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