ambra19
Board Regular


Joined: 09 May 2003
Posts: 36


Status: Offline

   

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
Board Master


Joined: 20 May 2002
Posts: 439
Location: Houston, TX
Flag:

Status: Offline

   

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
Board Regular


Joined: 09 May 2003
Posts: 36


Status: Offline

   

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
MrExcel MVP


Joined: 16 Feb 2002
Posts: 1381
Location: Denver, CO
Flag:

Status: Offline

   

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
Board Master


Joined: 20 May 2002
Posts: 439
Location: Houston, TX
Flag:

Status: Offline

   

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

jmiskey
Board Master



Joined: 02 Aug 2002
Posts: 3381

Flag:

Status: Offline

   

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.
_________________
Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!

ambra19
Board Regular


Joined: 09 May 2003
Posts: 36


Status: Offline

   

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

jmiskey
Board Master



Joined: 02 Aug 2002
Posts: 3381

Flag:

Status: Offline

   

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 | ...)
_________________
Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!

ambra19
Board Regular


Joined: 09 May 2003
Posts: 36


Status: Offline

   

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

jmiskey
Board Master



Joined: 02 Aug 2002
Posts: 3381

Flag:

Status: Offline

   

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.
_________________
Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!

ambra19
Board Regular


Joined: 09 May 2003
Posts: 36


Status: Offline

   

thanks jmiskey. That worked great.

texasalynn, i was wondering if you could let me know what I am missing from your example.
(I figure it's great to have a variety of solutions)

thanks again!
ambra19

texasalynn
Board Master


Joined: 20 May 2002
Posts: 439
Location: Houston, TX
Flag:

Status: Offline

   

What did you not understand from my example? The second post shows the formula's I used. Post back with more specific question

Thanks . . . texasalynn