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


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


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


Board Regular
Sep 1, 2008
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.


MrExcel MVP
Oct 14, 2016
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


Board Regular
Sep 1, 2008
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

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...