Date Command to Get the Sunday Before the First of the Month

[Minitman]

Greetings,

I have a weekly schedule that starts on the Sunday of the week of the
first day of the month (unless it starts on a Saturday). Since this
day will change almost every month, is there a special date command to
accomplish this in a cell formula?

Any help would be appreciated.

TIA

-Minitman.

[Peo Sjoblom]

What do you mean? Last Sunday of the month which is the Sunday before the
first of the month (your subject line)?
Or first Sunday of the month unless the first is a Saturday?

--
Regards,

Peo Sjoblom

(No private emails please)


"Minitman" <[email protected]> wrote in message
news:[email protected]...
> Greetings,
>
> I have a weekly schedule that starts on the Sunday of the week of the
> first day of the month (unless it starts on a Saturday). Since this
> day will change almost every month, is there a special date command to
> accomplish this in a cell formula?
>
> Any help would be appreciated.
>
> TIA
>
> -Minitman.

[Harlan Grove]

"Minitman" <[email protected]> wrote...
>I have a weekly schedule that starts on the Sunday of the week of the
>first day of the month (unless it starts on a Saturday). Since this
>day will change almost every month, is there a special date command to
>accomplish this in a cell formula?

Given a date D, the last day of the previous month is always given by

D-DAY(D)

The last Sunday of the previous month is always given by

D-DAY(D)-WEEKDAY(D-DAY(D),1)+1

IF the following Saturday is the first day of the next month, then the last
day of the previous month is Friday, so

WEEKDAY(D-DAY(D),1)=6

So when the first day of the month is a Saturday, I'd infer you'd want to
treat that as the last day of the final week of the preceding month. If so,
the first Sunday of the month is given by

=D-DAY(D)-WEEKDAY(D-DAY(D),1)+IF(WEEKDAY(D-DAY(D),1)=6,8,1)

[Minitman]

Hey Harlan,

That is exactly what I was looking for - Thank You!

-Minitman



On Thu, 1 Dec 2005 20:21:19 -0800, "Harlan Grove" <[email protected]>
wrote:

>"Minitman" <[email protected]> wrote...
>>I have a weekly schedule that starts on the Sunday of the week of the
>>first day of the month (unless it starts on a Saturday). Since this
>>day will change almost every month, is there a special date command to
>>accomplish this in a cell formula?
>
>Given a date D, the last day of the previous month is always given by
>
>D-DAY(D)
>
>The last Sunday of the previous month is always given by
>
>D-DAY(D)-WEEKDAY(D-DAY(D),1)+1
>
>IF the following Saturday is the first day of the next month, then the last
>day of the previous month is Friday, so
>
>WEEKDAY(D-DAY(D),1)=6
>
>So when the first day of the month is a Saturday, I'd infer you'd want to
>treat that as the last day of the final week of the preceding month. If so,
>the first Sunday of the month is given by
>
>=D-DAY(D)-WEEKDAY(D-DAY(D),1)+IF(WEEKDAY(D-DAY(D),1)=6,8,1)
>

[Minitman]

Hey Harlan,

There are two other dates that I need. The first day of the year for
the month in question and a way to tell if the year in question is a
leap year or not (This schedule I am making spans 13 years or more).

Your continued assistance is very appreciated. Thanks.

-Minitman


On Thu, 1 Dec 2005 20:21:19 -0800, "Harlan Grove" <[email protected]>
wrote:

>"Minitman" <[email protected]> wrote...
>>I have a weekly schedule that starts on the Sunday of the week of the
>>first day of the month (unless it starts on a Saturday). Since this
>>day will change almost every month, is there a special date command to
>>accomplish this in a cell formula?
>
>Given a date D, the last day of the previous month is always given by
>
>D-DAY(D)
>
>The last Sunday of the previous month is always given by
>
>D-DAY(D)-WEEKDAY(D-DAY(D),1)+1
>
>IF the following Saturday is the first day of the next month, then the last
>day of the previous month is Friday, so
>
>WEEKDAY(D-DAY(D),1)=6
>
>So when the first day of the month is a Saturday, I'd infer you'd want to
>treat that as the last day of the final week of the preceding month. If so,
>the first Sunday of the month is given by
>
>=D-DAY(D)-WEEKDAY(D-DAY(D),1)+IF(WEEKDAY(D-DAY(D),1)=6,8,1)
>

[Harlan Grove]

Minitman wrote...
>There are two other dates that I need. The first day of the year for
>the month in question . . .

=DATE(YEAR(D),1,1)

> . . . and a way to tell if the year in question is a
>leap year or not (This schedule I am making spans 13 years or more).
....

=MONTH(DATE(YEAR(D),2,29))=2

[Minitman]

Thanks Harlan, this will take care of that problem nicely.

-Minitman


On 2 Dec 2005 09:21:59 -0800, "Harlan Grove" <[email protected]> wrote:

>Minitman wrote...
>>There are two other dates that I need. The first day of the year for
>>the month in question . . .
>
>=DATE(YEAR(D),1,1)
>
>> . . . and a way to tell if the year in question is a
>>leap year or not (This schedule I am making spans 13 years or more).
>...
>
>=MONTH(DATE(YEAR(D),2,29))=2