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

C

Chuck M

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!
 
T

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
 
P

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.
 
B

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)



David Biddulph said:
Try =DATE(2007,month+1,1)-WEEKDAY(2007,month+1,1)
Click to expand...
 
S

Steven

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
 
C

Chuck M

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


Bob Phillips said:
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)
Click to expand...
 
Top