Calculate no of days to go back to reach a specific day name

[sts023]

I know this should be easy, but I keep tying myself in knots over this one.

I have a date selected by the User.
I'm trying to find a simple way of working out how many days before that date is a specific "day name".

For example if I take the date as 23/12/2019, I want to find out how many days to go back to find the previous "Tue".

In this case, as 23/12/2019 is a Monday, I need to go back 6 days to get to 17/12/2019.

Both the date and the "target day name" vary.

I know I can do it with lots of IF or SELECT statements, but I'm sure there must be a very simple formula to generate what I need, but I just can't figure out what the formula is.

Can any kind soul help?

 

[Special-K99]

=A1-if(weekday(a1,1)=3,a1,a1-weekday(a1+4))

 

[sts023]

Thnaks, but I'm sorry, it's VBA that I'm using, not a cell formula.

 

[sts023]

Also, the formula kindly supplied by Special-K99 is "day specific" - it always generates the previous Tuesday.
I need VBA code to take as arguments a date and a day name, and to generate the date of the "day name" prior to the supplied date.

 

[Tetra201]

Is this what you need?

Function PriorDay(d As Date, n As String) As Date
    PriorDay = d - 1 - (5 - Application.Match(n, Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"), 0) + Weekday(d)) Mod 7
End Function

 

[sts023]

Thanks Tetra201.
I'm working in Word VBA, so "Application.Match" doesn't seem to be available.
However, I've slightly recoded your excellent answer and added a test harness, which I post below in case any other struggling coders need your solution.
Thanks again!

Sub TestfunPriorDay()
Dim datRes As Date
Dim strD As String
  strD = "Tue"
  datRes = funPriorDay(Now(), strD)
  MsgBox "Last " & strD & " was " & datRes
End Sub 'TestfunPriorDay
Public Function funPriorDay(datBase As Date, _
                         strD As String) As Date
Dim intDay                  As Integer
  Select Case UCase(strD)
    Case "MON": intDay = 1
    Case "TUE": intDay = 2
    Case "WED": intDay = 3
    Case "THU": intDay = 4
    Case "FRI": intDay = 5
    Case "SAT": intDay = 6
    Case "SUN": intDay = 7
  End Select
  funPriorDay = datBase _
           - 1 _
           - (5 - intDay _
           + Weekday(dat)) Mod 7
End Function 'funPriorDay

 

[Tetra201]

Glad to have helped.