Last weekend of month

J

Jeremy

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
 
W

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
[email protected]
 
B

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)
 
R

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
Click to expand...

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

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


--ron
 
R

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
Click to expand...

Sleepy.

Previous answer should have been:

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


--ron
 
D

Daniel.M

Hi,

One more way (with your date in A1):

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

Regards,

Daniel M.
 
Top