[Dale[_4_]]

This is probably a very basic question so I apologise first, how would I
calculate the last sunday of a given month? Once I have that date, I need
the following Friday......

Thanks

[Lars-Åke Aspelin[_2_]]

On Sun, 30 Mar 2008 12:04:43 +0100, "Dale" wrote:

This is probably a very basic question so I apologise first, how would I
calculate the last sunday of a given month? Once I have that date, I need
the following Friday......

Thanks



With the month, e.g. 2008-03 in cell A1try the following formula:

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

The following Friday can be obtained by just adding 5 to the above

Hope this helps / Lars-Åke

[David Biddulph[_2_]]

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

"Dale" wrote in message
...
This is probably a very basic question so I apologise first, how would I
calculate the last sunday of a given month? Once I have that date, I need
the following Friday......

Thanks

[Mike H]

or

=A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1)),1,7,6,5,4,3,2)

"Dale" wrote:

This is probably a very basic question so I apologise first, how would I
calculate the last sunday of a given month? Once I have that date, I need
the following Friday......

Thanks

[Mike H]

all of which has got nothing to do with the question set by the OP!! who
wanted the last Sunday and not the first Monday!!

"Mike H" wrote:

or

=A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1)),1,7,6,5,4,3,2)

"Dale" wrote:

This is probably a very basic question so I apologise first, how would I
calculate the last sunday of a given month? Once I have that date, I need
the following Friday......

Thanks

[Dale[_4_]]

Thanks.....that's what I was looking for


"Lars-Åke Aspelin" wrote in message
...
On Sun, 30 Mar 2008 12:04:43 +0100, "Dale" wrote:

This is probably a very basic question so I apologise first, how would I
calculate the last sunday of a given month? Once I have that date, I need
the following Friday......

Thanks



With the month, e.g. 2008-03 in cell A1try the following formula:

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

The following Friday can be obtained by just adding 5 to the above

Hope this helps / Lars-Åke

[Teethless mama]

=EOMONTH(A1,0)-MOD(EOMONTH(A1,0)-1,7)
=EOMONTH(A1,0)-MOD(EOMONTH(A1,0)-1,7)+5

"Dale" wrote:

This is probably a very basic question so I apologise first, how would I
calculate the last sunday of a given month? Once I have that date, I need
the following Friday......

Thanks

[Ron Rosenfeld]

On Sun, 30 Mar 2008 12:04:43 +0100, "Dale" wrote:

This is probably a very basic question so I apologise first, how would I
calculate the last sunday of a given month? Once I have that date, I need
the following Friday......

Thanks


If you have Excel 2007, or in an earlier version, if you have the Analysis
ToolPak installed, with any date in A1,you can use:

Sunday

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

For the following Friday, merely add 5 to the above

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

If you get a #NAME error, check HELP for EOMONTH and it will tell you what to
do.
--ron

[Michael (Micky) Avidan]

Originally Posted by Ron Rosenfeld

On Sun, 30 Mar 2008 12:04:43 +0100, "Dale" wrote:

This is probably a very basic question so I apologise first, how would I
calculate the last sunday of a given month? Once I have that date, I need
the following Friday......

Thanks


If you have Excel 2007, or in an earlier version, if you have the Analysis
ToolPak installed, with any date in A1,you can use:

Sunday

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

For the following Friday, merely add 5 to the above

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

If you get a #NAME error, check HELP for EOMONTH and it will tell you what to
do.
--ron

Sorry for the "delay".

The following formula seens to be the shortest:

=INT((EOMONTH(A1,0)-1)/7)*7+1-2

Michael Avidan
“Microsoft®” MVP – Excel
ISRAEL