Formula for date output

[HRspec2021]

How do I find a formula that will output a certain date based on the date of the cell next to it? I want a formula that will give me a date that is either Monday of the same week of the date in the next cell, or Monday of the next week, depending on the day of the week which the date falls. For example, L7 is 7/20/21. I want a formula in M7 to put out 7/19/21 because 7/20/21 falls on a Tuesday. Also, L8 is 7/22/21. Because our cutoff is Wednesday (anything after Wednesday goes to the next week), I want M8 to put out 7/26/21. I hope this makes sense.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=WORKDAY.INTL(L7-2,1,"0111111")

 

[jasonb75]

Try this with the reference date in A1 (adjust to suit).

=WORKDAY.INTL(A1-3,1,"0111111")

 

[HRspec2021]

Thank you both!

 

[Fluff]

Glad we could help & thanks for the feedback.