Calculate the weekday of last FULL week of month

[Pete_UK]

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

 

[Ron Coderre]

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)

 

[Bernd P]

Hello,

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

Regards,
Bernd

 

[Rick Rothstein \(MVP - VB\)]

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

 

[jbarrington]

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.

 

[jbarrington]

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.

 

[jbarrington]

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.