Share via

Excel Date Formula for either Monday prior or Friday of same week

Anonymous
2022-07-06T21:16:01+00:00

I am in need of a formula that will show a logic that if date in cell A1 is...

  • Tue or Wed or Thu = Show Date as prior Monday or Friday in cell A2

Trying to set up a spreadsheet for work dates but we only have particular matters set for Monday and Friday. So say the date populated is 07/13/2022 - I would need it to fall back to 07/11/2022. But if the date was already set as 07/11 for it to remain 07/11 since it's a Monday. Same thing for Friday dates as well (if the date populated is a Friday let it stay Friday)

I would imagine something like this would work but I'm not sure what numbers to input into the equation.

=A1+CHOOSE(WEEKDAY(A1),1,3,2,1,4,3,2)

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2022-07-06T21:49:58+00:00

    Close:

    =A1+CHOOSE(WEEKDAY(A1),1,0,-1,-2,-3,0,-1)

    1 person found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-08-10T20:06:48+00:00

    Hello again! Quick update - how can I get this to avoid holidays? In the coming weeks, 09/05 is a holiday but the dates are falling on this date. Is there a way to make it fall back to the Friday prior if it's a holiday?

    0 comments
  2. Anonymous
    2022-07-06T22:20:21+00:00

    PERFECT! I am by no means any kind of expert in Excel lol I have no clue how the formula you gave was made, but it's perfect! Thank you so much!

    0 comments
  3. HansV 462.6K Reputation points MVP Volunteer Moderator
    2022-07-06T21:49:47+00:00

    How about

    =A1-WEEKDAY(A1,3)+4*(WEEKDAY(A1,3)<=3)

    0 comments