Excel VBA - Legal language problem - "the 22nd, 24th and 26th Friday in 2021" - don't know where to start

GertFoot

New Member
Joined
Feb 23, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi there, first post.

I have a year's worth (365 days) of dates in a range that can start at any point in a year but I need to show the WEEKDAYNUM equivalent for each WEEKDAY. For example, if the range starts at Monday 1/10/2022 I need to be able to show that that particular Monday is in fact the second Monday in 2022. I need to calculate this for all days of the week. It is for a legal document so I guess there is a precedent for describing dates, for example, in custody arrangements as the 4th, 6th, 8th, 10th and 12th Thursday (etc) in a calendar year - even though the contract does not start at the beginning of a calendar year.

I'm pretty stuck at this point, I hope this makes sense, I'm not even sure what terms I should be using to describe my problem. If I was working in calendar years counting the weekdays would be a trivial problem. I guess I could extend the range to the beginning of the range year but that seems really clunky. I'm hoping for a VBA formula or function.

Any help, suggestions or links to leads would be super appreciated.
 

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.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Forum!

Am I over-simplifying this?

=TEXT(A1,"dddd") will give you the day.
=INT((A1-DATE(YEAR(A1)-1,12,25))/7) will give you the incidence (1st, 2nd, 3rd etc) in that calendar year

Or do you want the opposite direction, e.g. determine the 20th Tuesday in 2021?
 
Solution

GertFoot

New Member
Joined
Feb 23, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Thankyou for your kind and informative welcome StephenCrump!

I do believe you have answered my question :biggrin:

Please permit me to make a fool of myself while I try to decipher this: =INT((A1-DATE(YEAR(A1)-1,12,25))/7)

INT will cast the result to an int
A1 Eg 1/2/2021 - take the date in A1 and subtract something.
Something: DATE(YEAR(A1)-1,12,25) - making Xmas day in the previous year, so 12/25/2020
So 1/2/2021 - 12/25/2020 / 7

I am afraid I am lost. If you would care to take a moment I would be very grateful.

Otherwise thank you very much for your rapid help.

Cheers
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,664
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I am not exactly sure what you want to show; a few more examples of what you require will help.
Do you require Ordinals like st nd etc. ?
Please consider the example shown below.
We can make the formula more concise and readable.
You can paste the example to a clean sheet; click on the icon below the F(x).
I used 2 named arrays
Named Array DOWS ={"Mon","Tue","Wed","Thu","Fri","Sat","Sun"}Mon 10-Jan-22
Named Array Ord ={1,2,3,4,21,22,23,24,31;"st","nd","rd","th","st","nd","rd","th","st"}

Date and Time 2021.xlsm
ABCDEFGHI
1
2StartWeekdayInterval123456
31-Jan-22Monday2Mon 10-Jan-22
415-Feb-22Tue2Tue 08-Mar-22Tue 22-Mar-22
5
6
7
8
91-Jan-22Mon22nd Mon 10-Jan-2022
6b
Cell Formulas
RangeFormula
E3E3=WORKDAY.INTL(A3-1,C3,"0111111")
G4,I4G4=WORKDAY.INTL($A4-1,G2,REPLACE("1111111",MATCH(LEFT($B4,3),DOWS,0),1,0))
E9E9=INT((DAY(WORKDAY.INTL(A9-1,C9,"0111111")+6)/7))&LOOKUP(E2,Ord)&" "&TEXT(WORKDAY.INTL(A9-1,C9,"0111111"),"ddd dd-mmm-yyy")
 

GertFoot

New Member
Joined
Feb 23, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thank you for your reply Dave Patton. I am just reading up on how to use formulas to calculate cell values with VBA. I will be back in a jiffy.
 

aRandomHelper

Active Member
Joined
Jan 14, 2021
Messages
278
Office Version
  1. 2016
Platform
  1. Windows
Try UDF:
Book1.xlsm
LM
1110-01-222nd Monday of 2022
1227-01-224th Thursday of 2022
1310-02-226th Thursday of 2022
1424-02-228th Thursday of 2022
1510-03-2210th Thursday of 2022
Sheets
Cell Formulas
RangeFormula
M11:M15M11=d(L11)


VBA Code:
Function d(r As Range)
    Dim dd As String, w As Integer
    If IsDate(r.Value) Then
        w = Format(r.Value, "ww")
        If Weekday(DateSerial(Year(r.Value), 1, 1), vbSunday) > Weekday(r.Value, vbSunday) Then w = w - 1
        Select Case w
            Case 1, 21, 31, 41, 51: dd = "st"
            Case 2, 22, 32, 42, 52: dd = "nd"
            Case 3, 23, 33, 43: dd = "rd"
            Case Else: dd = "th"
        End Select
        d = w & dd & Format(r.Value, " dddd of yyyy")
    End If
End Function
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I am afraid I am lost. If you would care to take a moment I would be very grateful.

Let's start at the beginning: Excel dates/times are just numbers, e.g. today 23 Feb 2021 is 44,250. I choose to format dates as D MMM YYYY so for me this will display as 23 Feb 2021. Someone in the U.S. might choose mm/dd/yy and the same number will display for them as 02/23/21. But it's the same number - 44250 - that is being stored in the cell.

Because the dates are just numbers, we can do arithmetic with them. This might help show what my formula is doing (given that 1 Jan is exactly 1 week after Christmas Day):

ABC
1Sat 1 Jan 20221Saturday
2Sun 2 Jan 20221Sunday
3Mon 3 Jan 20221Monday
4Tue 4 Jan 20221Tuesday
5Wed 5 Jan 20221Wednesday
6Thu 6 Jan 20221Thursday
7Fri 7 Jan 20221Friday
8Sat 8 Jan 20222Saturday
9Sun 9 Jan 20222Sunday
10Mon 10 Jan 20222Monday
11Tue 11 Jan 20222Tuesday
12Wed 12 Jan 20222Wednesday
13Thu 13 Jan 20222Thursday
14Fri 14 Jan 20222Friday
15Sat 15 Jan 20223Saturday
16Sun 16 Jan 20223Sunday
17Mon 17 Jan 20223Monday
18Tue 18 Jan 20223Tuesday
19Wed 19 Jan 20223Wednesday
20Thu 20 Jan 20223Thursday
21Fri 21 Jan 20223Friday
22Sat 22 Jan 20224Saturday
23Sun 23 Jan 20224Sunday
24Mon 24 Jan 20224Monday
Sheet1
Cell Formulas
RangeFormula
B1:B24B1=INT((A1-DATE(YEAR(A1)-1,12,25))/7)
C1:C24C1=TEXT(A1,"dddd")
 

GertFoot

New Member
Joined
Feb 23, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Thankyou for your reply aRandomHelper. I am overwhelmed by the generous responses on this site.
 

GertFoot

New Member
Joined
Feb 23, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Thank you StephenCrump. When I converted the formula to VBA I had to use DateSerial and it all became clearer. By choosing Xmas day you effectively 'reset' all day counts to zero and allowed for any year to start on any of the seven days. Then dividing by 7 gave us the number of times the day had appeared since the beginning of the year.

VBA Code:
= Int((DayOfWeek - DateSerial(Year(DayOfWeek) - 1, 12, 25)) / 7)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,046
Messages
5,639,753
Members
417,108
Latest member
Thein Than

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top