Photo by Kyle Glenn on Unsplash

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:

getting the first date of the year

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.

udays to go to the start of week 2

Sunday is the first day of the week, so we need to add 1.

Find the first Sunday after the first day of the year

This logic works also when the first day of the year is a Sunday, see below for the year 2034.

2034 starts on a Sunday

The next step (5) is get the date, this is a variaton on step 04:

the date of the Sunday that starts week 2

To check if it all goes well, we add an extra column, indeed it is week 2

Check

To get the start date we use the function below:

Getting the start date of a week

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.

One function

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,
Concatenate(TheYear,"/01/01").ToDate(),
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

My name is Christiaan and I support SMB with calculations (budgets and planning — dates are numbers as well) and I prefer using Coda to get the job done.

Not to forget: the Coda Community provides great insights for free once you add a sample doc. Paid consulting is often not the way to proceed.

Christiaan on: How to get a date with a week number?

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store