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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,394
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
4,768
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,248
Messages
5,657,603
Members
418,402
Latest member
sgray1

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