Help finding the date of the last Saturday of a given month

[Guest]

Hi,

I can't get my head around the calculation necessary to find the date of the
last Saturday in any given month. Any help will be greatly appreciated!

 

[Guest]

hi chuck try

=A1-WEEKDAY(A1)

where A1 is the date

 

[T. Valko]

Try this:

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

Where n = 1 for Sunday ...........7 for Saturday

Biff

 

[Guest]

sorry that should read

=eomonth(A1)-weekday(A1)

 

[David Biddulph]

Try =DATE(2007,month+1,1)-WEEKDAY(2007,month+1,1)

 

[PCLIVE]

If you want something really long, try this:

=IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5)))<>7,DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5))-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5))),DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5)))

The date you want the last Saturday of the month is in A1.

 

[Bob Phillips]

You missed a bit David

=DATE(2007,month+1,1)-WEEKDAY(DATE(2007,month+1,1))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Try =DATE(2007,month+1,1)-WEEKDAY(2007,month+1,1)

 

[David Biddulph]

Yes, absolutely right, Bob.
--
David Biddulph

You missed a bit David

=DATE(2007,month+1,1)-WEEKDAY(DATE(2007,month+1,1))

 

[Guest]

Find the last Saturday of the month for any date:

=EOMONTH(a1,0)-CHOOSE(WEEKDAY(EOMONTH(a1,0)),1,2,3,4,5,6,0)
Cell (A!)
5/11/07 05/31/07 =eomonth(a1,0)
5 =WEEKDAY(eomonth(a1,0))
CHOOSE(WEEKDAY(A1,0),1,2,3,4,5,6,0)
5/26/2007 = The Last Saturday

=WEEKDAY(eomonth(a1,0))
Sunday(1) through Saturday(7)
Sunday is 1 -1 = a Saturday
Mon is a 2 -2 = a Saturday
Tue is a 3 -3 = a Saturday
Wed is a 4 -4 = a Saturday
Thu is a 5 -5 = a Saturday
Fri is a 6 -6 = a Saturday
Sat is a 7 -0 = a Saturday

 

[Guest]

Sorry for the delayed response. This is just what I needed. Thanks to all
who replied!
--
Thanks.
Chuck M.

You missed a bit David

=DATE(2007,month+1,1)-WEEKDAY(DATE(2007,month+1,1))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)