Excel Formula, get the previous wednesday date

microsoft excel

I have the following formula,

=WORKDAY(TODAY(),-2)

Which gets me Jan 30th.

Is there anyway I can get Jan 30th without putting the -2 in this formula? I want he formula to always get the previous Wednesdays date, but I manually just subtract the todays workday to get the previous Wednesday.

Best Answer

For reference, the previously accepted answer wrongly outputs future date for some scenarios

This formula outputs the previous Wednesday.

=TODAY()-MOD(WEEKDAY(TODAY())+2,7)-1

If today is Wednesday, it outputs the previous Wednesday as well.

MOD(..., 7) always returns value from 0 to 6.

So, -MOD(...)-1 always subtracts a value of 1 to 7 (days) from TODAY().

(You can adjust the value +2 in the formula to get different weekdays)


Example:

  • Today is 2019-2-25 (Mon), outputs 2019-2-20 (Wed)
  • Today is 2019-2-26 (Tue), outputs 2019-2-20 (Wed)
  • Today is 2019-2-27 (Wed), outputs 2019-2-20 (Wed)
  • Today is 2019-2-28 (Thu), outputs 2019-2-27 (Wed)
Related Question