3

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.

1

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)
  • nice! this is more what I want. is there anyway to use current wednesday instead of reverting to previous? – excelguy Feb 25 at 14:41
  • yes, it will be even simpler. here you are: =TODAY()-MOD(WEEKDAY(TODAY())+3,7). the MOD(...) always return value from 0 to 6, so you can get result ranging from [today-6days] to [today]. – wilson Feb 26 at 2:47
8

How about:

=TODAY() - MOD(TODAY(), 7) + 4

Which means get first day of current week (Saturday), then add 4 = Wednesday, (optionally -7 for the previous week).

If you want the previous week (if you are still in Sunday or Monday), then use if for MOD(TODAY(), 7)

  • 1
    I'm having trouble following the explanation. Why is the first day of the current week a Saturday (as opposed to Monday or Sunday depending on what calendar system you use)? If you add 4 to a Saturday I can see that you get Wednesday, but why would -7 get you to the previous Wednesday? It seems to me that Saturday - 7 = Saturday. Note: I'm not questioning the formula, but merely the explanation that goes with it. – Jon Bentley Feb 1 at 18:07
  • @JonBentley the -7 is meant to be appended to the shown formula, not subsituted in for the +4. As to why MOD(TODAY(),7) produces this very helpful result, I'm also curious. – Alex M Feb 1 at 18:37
  • 1
    @AlexM Dates in excel are like an integer. Today, for example, is 43497. If you divide it by 7 using the MOD formula, will always return 6 if a day is Friday. This works because the very first day excel accepts. By dividing by 7, you guarantee that you will always get a number from 0-6 (Because 7 / 7 = 1, which returns nothing). 8/7 returns 1 for everyone and 1 not divided. – Moacir Feb 1 at 18:56
  • So now you went to the Saturday of this week(considering Saturday would be the first day), then the +4 corrects it to Wednesday – Moacir Feb 1 at 18:58
  • @Moacir thanks for the info; I suppose this is most likely fodder for a separate question, but I find it very curious (which just makes it fit with everything else about the way Excel works with dates in my experience, I guess) that though TODAY() returns the date 'formatted as a date' you're still able to use numerical operations against the underlying date value. Adding to my confusion, when I entered =MOD(TODAY(),7) into an empty cell, to test this behavior, it output 1/6/1900 - which I now understand means it returned the numeral 6 (as your formula expects), only formatted as a date. – Alex M Feb 1 at 19:58

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.