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

  • Thread starter Thread starter Minitman
  • Start date Start date
M

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.
 
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 said:
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?
Click to expand...

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
 
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).
Click to expand...
....

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