Getting a previous Monday (unless the date is a Monday)

[Berandon]

I saw a thread that asked for the prior Monday formula, but i used it and if the date is a Monday it goes to the previous Monday, How would i change the formula to be if Monday is today then leave it. I am new to using days of the week formula, so any explanation of how the formula breaks down so i can use it for other days of the weeks i may need. I mostly need previous Monday and following Friday and i have a working Friday one, but not the Monday one.

Thank you in advance for any help.

 

[arthurbr]

Perhaps =today()-WEEKDAY(today(),3)

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td]

Mon 01 Jan 2018​

[/td][td="bgcolor:#CCFFCC"]

Mon 01 Jan 2018​

[/td][td="bgcolor:#CCFFCC"]B1: = A1 + 1 - WEEKDAY(A1 - 6 - 2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

Wed 10 Jan 2018​

[/td][td="bgcolor:#CCFFCC"]

Mon 08 Jan 2018​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

Fri 02 Mar 2018​

[/td][td="bgcolor:#CCFFCC"]

Mon 26 Feb 2018​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

Sun 11 Mar 2018​

[/td][td="bgcolor:#CCFFCC"]

Mon 05 Mar 2018​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

Tue 13 Mar 2018​

[/td][td="bgcolor:#CCFFCC"]

Mon 12 Mar 2018​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

Mon 12 Mar 2018​

[/td][td="bgcolor:#CCFFCC"]

Mon 12 Mar 2018​

[/td][td][/td][/tr]
[/table]

For the on-or-prior nDay,

= A1 + 1 - WEEKDAY(A1 - 6 - nDay)

For Monday, nDay is 2

 

[offthelip]

assuming that E3 contains "=Today()"
then :
=E3-WEEKDAY(E3-1)+1

 

[Tetra201]

If your Excel has the WORKDAY.INTL function, here is a shorter formula:

=WORKDAY.INTL(TODAY()+1,-1,"0111111")

 

[Berandon]

Thank you all for the awesome suggestions. I got it working perfectly.