Formula to Calculate the Last Weekday of Previous Month

**andrewc** · 08-26-2010, 06:43 AM

Hi,

Can someone please suggest a formula to calculate, given any particular date, the last weekday in the PREVIOUS month.

Thanks!

**bd528** · 08-26-2010, 07:04 AM

Originally Posted by andrewc

Hi,

Can someone please suggest a formula to calculate, given any particular date, the last weekday in the PREVIOUS month.

Thanks!

If your date is in A1 then :-

Please Login or Register  to view this content.

Or for the day itself

Please Login or Register  to view this content.

**estige** · 08-26-2010, 07:12 AM

Hi, With the date in question in C4, this will give you the date of the last day in last month.
=DATE(YEAR(C4),MONTH(C4),1)-1

You can change the format to DDD or DDDD to give you weekday.
Another possibillity is to enclose the function above in the weekday function. This will return a number to represent the weekday:
=WEEKDAY(DATE(YEAR(C4),MONTH(C4),1)-1)
If needed you can then use lookup or vlookup to present the result the way you want.

**andrewc** · 08-26-2010, 07:20 AM

Perfect, thanks very much!

**daddylonglegs** · 08-26-2010, 07:32 AM

You can shorten bd528's suggestion to the following

=A1-DAY(A1)-MAX(0,WEEKDAY(A1-DAY(A1),2)-5)

or if you don't mind using WORKDAY function (which is an Analysis ToolPak add-in function in versions before 2007) you can use this

=WORKDAY(A1-DAY(A1)+1,-1)