First and Last Weekday Dates for Each Week of Month

[Gos-C]

Hi,

I need a formula in cells B1 to B5 to give the dates of the first workday, and a formula in cells D1 to D5 to give the dates of the last workday of each week of the month that is entered in A1 -- appearing in the format:

(A1)April (B1)__________ (C1)to (D1)__________

Thanks,
Gos-C

[Gos-C]

I have revised my post of yesterday. Any help?

Gos-C

[duane]

how's this?

a1 month of interest entered as text
b1 =IF(WEEKDAY(DATEVALUE(A1&" 1"),2)=6,DATEVALUE(A1&" 1")+2,IF(WEEKDAY (DATEVALUE(A1&" 1"),2)=7,DATEVALUE(A1&" 1")+1,DATEVALUE(A1&" 1")))

this assumes current year

d1 =6-WEEKDAY(B1,2)+B1

b2 =B1+8-WEEKDAY(B1,2)

b3 =B2+7 copy thru b5

d2 =D1+7 copy thru d5

[Gos-C]

Hi Duane,

Thanks for your kind response.

I am getting year 2001 instead of the current year. Can the formula reference the year -- entered in another cell? Also, the first workday is correct but the last workday is coming up as Saturday.

Thanks again,
Gos-C

[duane]

for the Saturday issue, perhaps this

d1 =6-WEEKDAY(B1,2)+B1

should be

d1 =5-WEEKDAY(B1,2)+B1

for the year issue

change

DATEVALUE(A1&" 1"),

to

DATEVALUE(A1&" 1, "&a2),

assuming cell a2 has the year in question in it (as a number)

or you could format cell a1 as date, mmmm

and input the date as 4/1/2001 for example

and then change to

DATEVALUE(A1)

[Gos-C]

Hi Duane,

I got this to work. Thanks for your help.

However, I am having difficulty getting this formula to work:

=IF(AN2+7<DATE(YEAR(DATE!E23),MONTH(B2)+1,0),AN2+7,IF(AND(AN2+7>=DATE(YEAR(DATE!E23),MONTH(B2)+1,0),OR(WEEKDAY(DATE(YEAR(DATE!E23),MONTH(B2)+1,0)<>6,WEEKDAY(DATE(YEAR(DATE!E23),MONTH(B2)+1,0)<>7)),DATE(YEAR(DATE!E23),MONTH(B2)+1,0),"")))

AN2 contains the first workday of the penultimate week.

DATE(YEAR(DATE!E23),MONTH(B2)+1,0) is EOMonth

Can anyone spot what I am doing wrong?

Thanks,
Gos-C