Listing Business Days in a column based on Month selected

[ambra19]

My spreadsheet has a dropdown box where I can select the month and it will update Rows 6 thru 27 with business days only. It will also underline the Friday date (from Columns A-G) or the last day. It will also remove the underline for the previous month.
ie
April 2004 selected:
Apr 1/04
Apr 2/04 - underline
Apr 5/04
Apr 6/04
Apr 7/04
Apr 8/04
Apr 9/04 - underline
etc.

If any further clarification is required, please let me know.
Regards,
Karim

[texasalynn]

Not exactly sure what your question is. Are you trying to find a way to get the Friday's underlined? If so use the conditional format and weekday formula. In the conditional format make it =WEEKDAY($A2,2)=5 and choose the format to underline.

HTH
texasalynn

[ambra19]

I would like it to automatically list all of the eligible work dates in Column A when I select a particular month.
I don't want to have to type out the dates:

Apr 1/04
Apr 2/04
Apr 5/04
Apr 6/04
Apr 7/04

Thanks.

[IML]

assuming your drop down date (as text) is in cell a1, you could use
=WORKDAY(A1-1,1)
in cell A6
in a7 type
=IF(ISNUMBER(A6),IF(MONTH(WORKDAY(A6,1))=MONTH(--$A$1),WORKDAY(A6,1),""),"")
and copy down a goodly # of rows. Conditional formatting can be applied to these dates as suggested.

[texasalynn]

I did this - and so each time I type in a different date in A2 it automatically changes the dates.

******** ******************** ************************************************************************>

Microsoft Excel - Book2 ___Running: xl2000 : OS = Windows 98
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A2A3A4A5A6A7A8A9A10A11A12A13A14A15A16A17A18A19A20A21A22A23 =
	A	B	C	D
1	Jan-04	*	*	*
2	01/01/04	*	*	*
3	01/02/04	*	*	*
4	01/05/04	*	*	*
5	01/06/04	*	*	*
6	01/07/04	*	*	*
7	01/08/04	*	*	*
8	01/09/04	*	*	*
9	01/12/04	*	*	*
10	01/13/04	*	*	*
11	01/14/04	*	*	*
12	01/15/04	*	*	*
13	01/16/04	*	*	*
14	01/19/04	*	*	*
15	01/20/04	*	*	*
16	01/21/04	*	*	*
17	01/22/04	*	*	*
18	01/23/04	*	*	*
19	01/26/04	*	*	*
20	01/27/04	*	*	*
21	01/28/04	*	*	*
22	01/29/04	*	*	*
23	01/30/04	*	*	*
Sheet1 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

HTH
texasalynn

[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.

[ambra19]

I tried both suggestions and have received a #NAME? error.
What am I missing?
The help is greatly appreciated.
ambra19

[Joe4]

The EOMONTH and WORKDAY functions require the Analysis Tool-Pak Add-in to be installed. Many times, it will be installed and just needs to be selected (Tools | Add-ins | ...)

[ambra19]

I activate the Add-In.
The EOMONTH is close to working in that the month gets changed rather than the day.
The other version:
=IF(MONTH(WORKDAY($A$1-1,ROW()))=MONTH($A$1),WORKDAY($A$1-1,ROW()),"")
The first I get is 38078 followed by #VALUE? in every row thereafter in Column B. If I copy this under the Date in A1, I get 38078 followed by increments of one as the rows increase.

Thanks again for all of the help.
ambra19

[Joe4]

38078 IS your date. Excel stores all dates as numbers. You simply need to format the column as dates and everything will look as you want it to.