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

sts023

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

sts023

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

sts023

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

Threads
1,078,435
Messages
5,340,250
Members
399,361
Latest member
Linford

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...
Top