Calculate the weekday of last FULL week of month

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jbarrington - 19 Dec 2007 00:32 GMT

I’ve seen formulas where they figure the “nth” day of the month, but I
haven’t been able to find a calculation that does what I’m looking for.

I’m having a problem trying to figure out how to calculate what a
certain weekday is of the last FULL week of the month.
(stressing the LAST FULL WEEK)

Thanks for any help.

Reply to this Message

Pete_UK - 19 Dec 2007 01:20 GMT

What do you mean by the "last full week"? Taking this month as an
example, the last full week could be defined as the final seven days
in the month, i.e. from Tuesday 25th Dec to Monday 31st Dec, so if
this is what you mean then which weekday do you want? Is it the day
which is seven days away from the last day of the month? (i.e. the
same day as the 1st of next month)

Or is it something else entirely?

Pete

> I've seen formulas where they figure the "nth" day of the month, but I
> haven't been able to find a calculation that does what I'm looking for.
[quoted text clipped - 4 lines]
>
> Thanks for any help.

Reply to this Message

Ron Coderre - 19 Dec 2007 02:16 GMT

With
A1: (a date.....eg 02/15/2007)
A2: (a weekday to find...1=Sun, 2=Mon, 3=Tue...7=Sat)

This formula returns the referenced WEEKDAY from A2
that is in the last FULL WEEK OF THE MONTH including that
includes the date in A1.

A3: =A1-DAY(A1)+MAX(DAY(A1+1-WEEKDAY(A1+1-7)+7*{1;2;3;4;5}))-(7-A2)

Note: Weeks are Sunday through Saturday.

Example:
A1: 2/15/2007
A2: 3......indicating Tuesday

A3 returns: Tuesday, 02/20/2007
(note: the last Saturday of Feb-2007 is 02/24/2007)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

> I’ve seen formulas where they figure the “nth” day of the month, but I
> haven’t been able to find a calculation that does what I’m looking for.
[quoted text clipped - 4 lines]
>
> Thanks for any help.

Reply to this Message

jbarrington - 19 Dec 2007 09:43 GMT

Thanks Ron & Pete for replying

I was wanting to use it to calculate an unofficial holiday. The day is
Administrative Professionals Day (formerly Secretary's Day), and it is
celebrated on the last Wednesday of the last full week of April in the
USA (I'm not sure about other countries).

I know rhere are some calendars that show Monday as the start of the
full week and Sunday as the end. Other calendars show Sunday as the
start of the full week with Saturday as the end. I think the more
accepted/traditional calendars are the second example that I gave, but
I'm really unsure how one would truly declare it to figure this out. I
did think of a compromise of just leaving out Sunday, but I don't know
if that would just mess up the true day of the holiday date.

I don't know if that information/explanation helps Pete understand what
I was after, or changes what Ron gave me as a formula suggestion.

Ron, I'll give you suggestion a try today when I get to work. I'll check
back here again after work to see if either of you (or someone else) has
added something else.

Many thanks.

> With
> A1: (a date.....eg 02/15/2007)
[quoted text clipped - 32 lines]
>>
>> Thanks for any help.

Reply to this Message

Bernd P - 19 Dec 2007 13:27 GMT

Hello,

=DATE(YEAR(A1),5,0)-CHOOSE(WEEKDAY(DATE(YEAR(A1),5,0)),4,5,6,7,8,9,3)

Regards,
Bernd

Reply to this Message

Rick Rothstein (MVP - VB) - 19 Dec 2007 16:02 GMT

This will calculate that "holiday":

If A1 contains a date with or without the day...
=DATE(YEAR(A1),5,0)-MOD(DATE(YEAR(A1),5,0),7)-3

If A1 contains the year only...
=DATE(A1,5,0)-MOD(DATE(A1,5,0),7)-3

Rick

> Thanks Ron & Pete for replying
>
[quoted text clipped - 57 lines]
>>>
>>> Thanks for any help.

Reply to this Message

jbarrington - 19 Dec 2007 21:56 GMT

Many, many thanks for the replies that wanted to help. The formula:

=DATE(A1,5,0)-MOD(DATE(A1,5,0),7)-3

was the closest solution that seems to provide the answer I need.

Again, thanks.

Reply to this Message