Last Saturday of the Month

[AdminCo]

Hello,

I am trying to get cell A4 to return the last Saturday of the month based on the date in cell A1 being the month i.e. September 01, 2003. Then in cell A2 to show the Friday just over one week before the last Saturday (8 days).

Cell A1 = September 1, 2003 (Entered)
Cell A2 = September 19, 2003 (Formula)
Cell A4 = September 27, 2003 (Formula)

Any help would be appreciated.

Thanks,
Alan

 

[eliW]

This also should work:

=IF(WEEKDAY(EOMONTH(A1,0))=7,EOMONTH(A1,0),EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0)))

Eli

 

[eliW]

And even shorter:

=EOMONTH(A1,0)-MOD(WEEKDAY(EOMONTH(A1,0)),7)

Eli

 

[Yogi Anand]

Here is another without the use of EOMONTH function ...

=DATE(YEAR(A1),MONTH(A1)+1,0)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0))

 

[AdminCo]

What a Team. Thanks very much to the lot of you. It sure is good to see so many versions and ideas of how to skin a cat. Not hard to see why this web-site IS the Number One Excel location on this earth...

Again...Thanks

 

[rrdonutz]

originally posted by eliW
And even shorter:

=EOMONTH(A1,0)-MOD(WEEKDAY(EOMONTH(A1,0)),7)

Eli

Even shorter still (based on Eli's slick formula above), with one less function call:

=EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0)+1)+1

--Tom

 

[Yogi Anand]

Hi Tom:

Talking about shorter -- how about shortening it to ...

=EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0))

 

[rrdonutz]

Hi Yogi,

Talking about shorter -- how about shortening it to ...

=EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0))

This won't always work, as it will sometimes return the 4th Saturday of the month when there are 5 total. Example: May 2003.

For anybody interested, a variation of the above formula to find any particular last weekday of the month is:

=EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0)+Num)+1

where Num = {0,1,2,3,4,5,6} for {Sun,Sat,Fri,Thurs,Wed,Tues,Mon}, respectively. Of course, the 0 can be omitted for Sunday.

--Tom