@Alyssa
Seeing your response jolted me to create this equation that will tell me the first Monday of the year.
Please see below.
// Find the first Monday in January of this year.
// Monday=2; Therefore 7+2=9.
// Adding 1 to the MOD remainder gives me the date
=DATE(YEAR(TODAY()),1,1+MOD(9-WEEKDAY(DATE(YEAR(TODAY()),1,1)),7))
From this, I'll be able to know how many weeks have passed since week 1.
// Dividing the number of dates and rounding down the remainder gets the number of whole weeks since start date of the year.
=1+ROUNDDOWN(DATEDIF([Formula above specifying start date],TODAY(),"D")/7,0)
=> 1+ROUNDDOWN(DATEDIF([Formula above specifying start date],[Date to evaluate],"D")/7,0)
So this is the complete formula which gets me the WeekOfYear()
=1+ROUNDDOWN(DATEDIF(DATE(YEAR([Date to evaluate]),1,1+MOD(9-WEEKDAY(DATE(YEAR([Date to evaluate]),1,1)),7)),[Date to evaluate],"D")/7,0)
If [Date to evaluate] is 17/1/2021, it'll be Week 2.
I've only just written it minutes before responding to you, so if this helps any honeycoder out there, that'll be great, but don't ping me if it doesn't work for a really odd year. I've only tested this on 2017 to 2021.
This way I won't have to worry about updatng a table which stores the weeks! However, cross-year dates might turn out funky. shrugs Then it's gotta be Week 53! lol
This is how it's written in my application
=IFERROR((1+ROUNDDOWN(DATEDIF(DATE(YEAR([Appointment][Date]),1,1+MOD(9-WEEKDAY(DATE(YEAR([Appointment][Date]),1,1)),7)),[Appointment][Date],"D")/7,0)),53)