Last weekend of month

[Guest]

Hi. I want to create a function in a column that looks at a date in another
cell and tests to see if that date is the last friday, saturday, or sunday of
the month.

basically, if the date is the last friday, or the last saturday, or the last
sunday of the month, I would like a 1 in my new column, and if not, a 0.

Thanks,
Jeremy

 

[William]

Hi Jeremy

Assuming the date you want to test is in F1, you could try this formula...
=AND(MONTH(F1)<>MONTH(F1+7),OR(WEEKDAY(F1)>5,WEEKDAY(F1)<2))*1

--

XL2003
Regards

William
(e-mail address removed)

 

[Bob Phillips]

Hi Jeremy,

Try

=IF(AND(OR(WEEKDAY(A2)=1,WEEKDAY(A2)>5),MONTH(A2)<>MONTH(A2)+1),1,0)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ron Rosenfeld]

Hi. I want to create a function in a column that looks at a date in another
cell and tests to see if that date is the last friday, saturday, or sunday of
the month.

basically, if the date is the last friday, or the last saturday, or the last
sunday of the month, I would like a 1 in my new column, and if not, a 0.

Thanks,
Jeremy

If your date to be tested is in A1, then:

=--AND(MONTH(A1+7)<>MONTH(A1),OR(WEEKDAY(A1,3)={4,5,6}))


--ron

 

[Ron Rosenfeld]

Hi. I want to create a function in a column that looks at a date in another
cell and tests to see if that date is the last friday, saturday, or sunday of
the month.

basically, if the date is the last friday, or the last saturday, or the last
sunday of the month, I would like a 1 in my new column, and if not, a 0.

Thanks,
Jeremy

Sleepy.

Previous answer should have been:

=(MONTH(A1+7)<>MONTH(A1))*(WEEKDAY(A1,3)>=4)


--ron

 

[Daniel.M]

Hi,

One more way (with your date in A1):

=(DAY(A1+7)<8)*(WEEKDAY(A1,2)>4)

Regards,

Daniel M.