Sunday
 

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

Sunday
 

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

Sunday
 

=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

Sunday
 

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

Sunday
 

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

Sunday
 

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

Sunday
 

=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

Sunday
 

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