How to get a date with a week number?
Using Coda to get the start and end date of week
In the community an interesting question came up that was a bit harder to crack than the author might have foreseen. I understood the question as follows:
Give me the start date and the end date of any week based on a week number.
With only a week number, this is not going to happen, we need one additional piece of information: the year.
To solve this little puzzle I work with a few steps, in the end I integrate them to have one larger function you can copy — paste to apply in your work.
The first day of the year
We start with the creation of the first date of the year using the below logic:
The text box helps to check for various years fast.
Next we define the number of the day of this first date. Since most readers use the USA date notation and assume Sunday as the first day of the week, I adapted my settings accordingly. The first of January 2022 was a Saturday, that is day number 7. The third step is to count the days to go until the start of week 2. Below I used the year 2020, for 2022 the outcome is 0.
Sunday is the first day of the week, so we need to add 1.
This logic works also when the first day of the year is a Sunday, see below for the year 2034.
The next step (5) is get the date, this is a variaton on step 04:
To check if it all goes well, we add an extra column, indeed it is week 2
To get the start date we use the function below:
We take the week number we want to use, we subtract 2 and we divide by 7 (days a week). In case this is a negative number — meaning a date in the previous year — , we take the first day of the year. When it is a positive number (most of the time) we take the date we get. We are almost there. To get the end of the week, we add 7 days to the date we found as ‘first date’. That this date is in a previous year, does not matter.
Voila this is it in many small steps.
We can shorten the above logic by gluing the described steps together. I applied the step logic inside the function to keep it readable. I admit, it is a bit messy, but it does the job. The only part you have to adapt in your specific scenario is the year logic.
Below the code you can paste in your table:
Concatenate(TheYear,"/01/01").ToDate().WithName(step01,step01.Weekday().WithName(Step02,7 - Step02).WithName(Step03,Sum(step01,Step03,1).ToDate())).WithName(Step05,Step05.WeekNumber().WithName(step06,If(
Product(thisRow.TheWeekNumber - step06,7) < 0,
Product(thisRow.TheWeekNumber - step06,7) + Step05)))
The end date is more or less the same. I kept it simple, duplicated the column, removed the If statement and replaced it with the part in yellow:
This was an interesting puzzle and shows the great power of Coda.
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter