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

#### sts023

##### Board Regular
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?

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Special-K99

##### Well-known Member
=A1-if(weekday(a1,1)=3,a1,a1-weekday(a1+4))

Last edited:

#### sts023

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

#### sts023

##### Board Regular
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

##### MrExcel MVP
Is this what you need?
Code:
``````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

##### Board Regular
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!
VBA Code:
``````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``````