Sunday

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dale - 30 Mar 2008 12:04 GMT

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

Reply to this Message

Lars-Åke Aspelin - 30 Mar 2008 12:45 GMT

>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

Reply to this Message

Dale - 30 Mar 2008 14:23 GMT

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

>>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
[quoted text clipped - 9 lines]
>
> Hope this helps / Lars-Åke

Reply to this Message

David Biddulph - 30 Mar 2008 12:46 GMT

=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

> 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

Reply to this Message

Mike H - 30 Mar 2008 13:06 GMT

or

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

> 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

Reply to this Message

Mike H - 30 Mar 2008 13:13 GMT

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

> or
>
[quoted text clipped - 5 lines]
> >
> > Thanks

Reply to this Message

Teethless mama - 30 Mar 2008 14:39 GMT

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

> 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

Reply to this Message

Ron Rosenfeld - 30 Mar 2008 14:42 GMT

>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

Reply to this Message