Show a list of weekday dates excluding weekends

[lewdow]

Hi,

I need to show a dynamic list of dates in the current month, but excluding weekends e.g.

1/4/10
2/4/10
5/4/10
6/4/10
7/4/10
8/4/10
9/4/10
12/4/10

I am able to get a full list of dates as shown below, and I can also identify which dates are weekends, however I need a continuous list excluding weekends.

Current setup is as below:

A1:A31 = Numbers 1-31 (although this will only need to be 22ish when weekends are exluded)

B1:B31 = =IF(MONTH(TODAY()-DAY(TODAY())+A1)<>MONTH(TODAY()),"#N/A",TODAY()-DAY(TODAY())+A1). This is to show the list of all dates in the month, and if the 31st day is actually the first of the previous month

C1:C31 = =IF(IF(OR(WEEKDAY(TODAY()-DAY(TODAY())+A1)=1,WEEKDAY(TODAY()-DAY(TODAY())+A1)=7),"WEEKEND",TODAY()-DAY(TODAY())+A1)="WEEKEND",C3,IF(OR(WEEKDAY(TODAY()-DAY(TODAY())+A1)=1,WEEKDAY(TODAY()-DAY(TODAY())+A1)=7),"WEEKEND",TODAY()-DAY(TODAY())+A1)). This currently tells me the day (Thu, Fri etc) of the date, and if the day is Sat or Sun it shows "weekend".

I dont need to show 'Weekend', but I need to have a continuous list of Mon, Tue, Wed, Thu, Fri, Mon, Tue, Wed, Thu, Fri, Mon.........

Your help you be greatly appreciated as this has currently frustrated me for a good few hours!

Thanks

Lewis

 

[barry houdini]

Here's one way to do that using WORKDAY function. WORKDAY is a native function in Excel 2007 but in earlier versions you need Analysis ToolPak installed

Start with the dates in column B...

In B1 to get the first workday of the current month

=WORKDAY(TODAY()-DAY(TODAY()),1)

Then for all subsequent workdays put this formula in B2 and copy down to B23

=IF(B1="","",IF(MONTH(WORKDAY(B1,1))<>MONTH(B1),"",WORKDAY(B1,1)))

Format column B in required date format

For the number of each workday use this formula in A1 copied down to A23

=IF(B1="","",COUNT(B$1:B1))

and for the weekday in column C use this formula in C1 copied to C23

=IF(B1="","",TEXT(B1,"ddd"))

Note that row 23 will be blank for April 2010 but it'll get use when needed in subsequent months

 

[barry houdini]

Using WORKDAY also allows you to have a list of holiday dates somewhere and exclude those dates from your list.....but if you don't want to use WORKDAY, or you can't, then you could use these formulas for B1 and B2 copied down respectively

=CHOOSE(WEEKDAY(TODAY()-DAY(TODAY())),1,1,1,1,1,3,2)+TODAY()-DAY(TODAY())

and

=IF(B1="","",IF(MONTH(IF(WEEKDAY(B1)=6,3,1)+B1)<>MONTH(B1),"",IF(WEEKDAY(B1)=6,3,1)+B1))