A couple of weeks ago I was talking about dates during an Excel class and someone asked if it’s possible to calculate the date of the last Friday of each month. The reason being that for them that was pay day.

Someone else asked if Excel could calculate the date of the last Wednesday of each month because that was the deadline in their company for expense claims to be submitted.

The answer to both of those questions is “Yes”. So that’s what this video is about…how to calculate the date of the last Friday of of each month. But I’ll also show you how, with a small tweak, you can change Friday for Wednesday or Thursday or in fact any day of the week.

There’s several ways that you can do this. I’ve seen solutions using the WORKDAY.INTL function. I’ve seen solutions using the MOD function. But I’ve opted for a slightly longer method, the logic being that for the everyday intermediate-level Excel user I think it’s easier to understand.

Formulas used in the video:

=EOMONTH(DATE($A$1,C2,0))

=TEXT(C3,”DDDD”)

=XLOOKUP(C4,$B$14:$B$20,$C$14:$C$20)

=C3-C5

Download the demo file used in the video

https://app.box.com/s/k1wp7ck7uiz24arg3kwy18hy76um6l4s