**Last Weekday**

Sometimes floating dates are calculated as the

**second Sunday**in

**May**or perhaps the

**third Thursday**in

**November**. But sometimes they are also calculated as the

**last weekday**such as the

**final Saturday**in

**October**. Here is a formula that can be used in this case, where

**A1**is the Year,

**A2**is the

**Month**, and

**A3**is the

**Weekday**(

**Weekdays**are entered as numbers,

**Sunday**=

**1**,

**Monday**=

**2**...

**Saturday**=

**7**)

=IF(A2<>MONTH(DATE(A1,A2+1,0)-(WEEKDAY(DATE(A1,A2+1,0))-A3)),DATE(A1,A2+1,0)-(WEEKDAY(DATE(A1,A2+1,0))-A3)-7,DATE(A1,A2+1,0)-(WEEKDAY(DATE(A1,A2+1,0))-A3))

Here is a formula you don't want to specify the year, it always uses the current year.

=IF(A2<>MONTH(DATE(YEAR(TODAY()),A2+1,0)-(WEEKDAY(DATE(YEAR(TODAY()),A2+1,0))-A3)),DATE(YEAR(TODAY()),A2+1,0)-(WEEKDAY(DATE(YEAR(TODAY()),A2+1,0))-A3)-7,DATE(YEAR(TODAY()),A2+1,0)-(WEEKDAY(DATE(YEAR(TODAY()),A2+1,0))-A3))

**Shortened Formula**

Nobody suggested any shortened versions of the formula in my previous post but I had a quick look myself and saw that the first part of the formula is redundant with regards to the

**MONTH**function and changed it to this.

=IF(A2<>MONTH(IF(WEEKDAY(DATE(A1,A2,1)-(WEEKDAY(DATE(A1,A2,1))-A3))<WEEKDAY(DATE(A1,A2,1)),DATE(A1,A2,1)+(A3-WEEKDAY(DATE(A1,A2,1)))+7,DATE(A1,A2,1)-(WEEKDAY(DATE(A1,A2,1))-A3))),"",IF(WEEKDAY(DATE(A1,A2,1)-(WEEKDAY(DATE(A1,A2,1))-A3))<WEEKDAY(DATE(A1,A2,1)),DATE(A1,A2,1)+(A3-WEEKDAY(DATE(A1,A2,1)))+7,DATE(A1,A2,1)-(WEEKDAY(DATE(A1,A2,1))-A3)))

And here is the version that always use the current year.

=IF(A2<>MONTH(IF(WEEKDAY(DATE(YEAR(TODAY()),A2,1)-(WEEKDAY(DATE(YEAR(TODAY()),A2,1))-A3))<WEEKDAY(DATE(YEAR(TODAY()),A2,1)),DATE(YEAR(TODAY()),A2,1)+(A3-WEEKDAY(DATE(YEAR(TODAY()),A2,1)))+7,DATE(YEAR(TODAY()),A2,1)-(WEEKDAY(DATE(YEAR(TODAY()),A2,1))-A3))),"",IF(WEEKDAY(DATE(YEAR(TODAY()),A2,1)-(WEEKDAY(DATE(YEAR(TODAY()),A2,1))-A3))<WEEKDAY(DATE(YEAR(TODAY()),A2,1)),DATE(YEAR(TODAY()),A2,1)+(A3-WEEKDAY(DATE(YEAR(TODAY()),A2,1)))+7,DATE(YEAR(TODAY()),A2,1)-(WEEKDAY(DATE(YEAR(TODAY()),A2,1))-A3)))

Not much shorter, but getting there... ;-)

Don't forget you can use these formulas to find the first or last days in a month also.