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.

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" wrote in message
...
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.

"Minitman" 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)

Hey Harlan,

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

-Minitman



On Thu, 1 Dec 2005 20:21:19 -0800, "Harlan Grove"
wrote:

"Minitman" 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)

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"
wrote:

"Minitman" 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 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

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

-Minitman


On 2 Dec 2005 09:21:59 -0800, "Harlan Grove" 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