Formula for Calculating a Floating Date
 

Hi All

I need a formula to calculate a floating date - the last Saturday in
September

If found this formula on another site, but couldn't seem to get it to
work.

=SUM((WEEKDAY(DATE(B3,C3,(ROW(INDIRECT(
"1:"&DAY(DATE(B3,C3+1,0)))))))=D3)*1)

Where B3 is the year, C3 is the month, and D3 is the day of week
(1=Sunday, 2=Monday, ..., 7=Saturday

Any help in either what I have done wrong or an alternative formula
would be appreciated.

Ta

Karen


--
ksp
------------------------------------------------------------------------
ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267
View this thread: http://www.excelforum.com/showthread...hreadid=398010

Assuming a "year" in cell A1 (like 2005, 2004, 2003, etc.), this will
work to find the "last Saturday in September" in the year in A1:

=IF( WEEKDAY( DATE( A1, 9, 30)) < 7, DATE( A1, 9, 30) - WEEKDAY( DATE(
A1, 9, 30)), DATE( A1, 9, 30))

Chris

ksp wrote:
Hi All

I need a formula to calculate a floating date - the last Saturday in
September

If found this formula on another site, but couldn't seem to get it to
work.

=SUM((WEEKDAY(DATE(B3,C3,(ROW(INDIRECT(
"1:"&DAY(DATE(B3,C3+1,0)))))))=D3)*1)

Where B3 is the year, C3 is the month, and D3 is the day of week
(1=Sunday, 2=Monday, ..., 7=Saturday

Any help in either what I have done wrong or an alternative formula
would be appreciated.

Ta

Karen


--
ksp
------------------------------------------------------------------------
ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267
View this thread: http://www.excelforum.com/showthread...hreadid=398010

Thanks Chris that worked a treat

Much appreciated


--
ksp
------------------------------------------------------------------------
ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267
View this thread: http://www.excelforum.com/showthread...hreadid=398010

On Mon, 22 Aug 2005 21:47:29 -0500, ksp
wrote:


Hi All

I need a formula to calculate a floating date - the last Saturday in
September

If found this formula on another site, but couldn't seem to get it to
work.

=SUM((WEEKDAY(DATE(B3,C3,(ROW(INDIRECT(
"1:"&DAY(DATE(B3,C3+1,0)))))))=D3)*1)

Where B3 is the year, C3 is the month, and D3 is the day of week
(1=Sunday, 2=Monday, ..., 7=Saturday

Any help in either what I have done wrong or an alternative formula
would be appreciated.

Ta

Karen

With any date in the desired month in A1:

=A1-DAY(A1)+33-DAY(A1-DAY(A1)+32)-
WEEKDAY(A1-DAY(A1)+33-DAY(A1-DAY(A1)+32))

or:

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


--ron