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

sts023

Board Regular
Joined
Sep 1, 2008
Messages
106
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?
 

Some videos you may like

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.

sts023

Board Regular
Joined
Sep 1, 2008
Messages
106
Thnaks, but I'm sorry, it's VBA that I'm using, not a cell formula.
 

sts023

Board Regular
Joined
Sep 1, 2008
Messages
106
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
Joined
Oct 14, 2016
Messages
3,542
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
Joined
Sep 1, 2008
Messages
106
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,552
Messages
5,487,524
Members
407,604
Latest member
sama9000

This Week's Hot Topics

Top