6. August 2022
0 Minute
Laptop

Elusive Mondays

This little story starts, like so many others, with a call from a customer. The project itself was fairly simple: create a solution that enables us to notify people on call at out of office hours. We created a small Power App, a more or less slightly glorified form for entries into a calendar and an Azure Logic App with a HTTP Trigger to query said calendar and notify the people on call that day.

When seeing the Power App the customer said something along the lines of „Since all our employees are always scheduled for a whole week, we’d like to select only the week and have all the dates set automatically.“, with me replying „Yeah, that shouldn’t be too much of a problem.“.

Oh how wrong I had been. It turned out that mondays, though frequently found, to ones pleasure or despair, at the beginning of every week, are more elusive than I imagined. Especially the correct date of a monday when only the week is selected.

 

Searching for an easy solution

There are functions in Power Apps to do lots of ‚fun‘ things, but alas, there is none that returns the date of the monday of a week when selecting just the week’s number. So I did what everyone does first – ask Dr. Google. But what I discovered didn’t meet my needs. I found a post in the community forums, but that didn’t cut it for me. If you are interested in that solution you can find it here: Obtain-Dates-from-Weeknumber​​​​​​​

I wanted to have my dropdown with years and a drop down with the weeks of that year. Select the year, then the week and boom…the date gets calculated automatically.

 

Inching forward

The first step in was to have the two dropdowns for the year and the week. Those where pretty simple:

​​​​​​​For the year I added a dropdown control and for items I used:

𝚂𝚎𝚚𝚞𝚎𝚗𝚌𝚎(𝟻𝟶,𝟸𝟶𝟸𝟷,𝟷)
which created a table with 50 entries starting from 2021 up to 2071, which should be more than enough years to choose from.

The dropdown for the weeks was created in a similar fashion:

𝚂𝚎𝚚𝚞𝚎𝚗𝚌𝚎(𝟻𝟸,𝟷,𝟷)
Unfortunately there are years with 52 and years with 53 weeks and the difference is which weekday is the first day of the year.

And I was today years old when I learned that if a year starts with a Thursday it has 53 weeks, else 52.

Aside from differentiating between the 53 and 52 week years, I needed to know how many days are there until the first monday of the year, because a simple

𝚍𝚛𝚙_𝚢𝚎𝚊𝚛.𝚂𝚎𝚕𝚎𝚌𝚝𝚎𝚍.𝚅𝚊𝚕𝚞𝚎*𝟽
was obivously not the solution to my problem. I needed to factor in the ‚offset“ due to different first days of the different years.

So I created a Label control to hold that part of the formula… as the headline reads, I was inching forward towards my goal.

I figured that if the first day of a year was a monday I wouldn’t have to do lots of adjustments to find the first monday of the year (obviously), if the first day of the year was a Tuesday though, I would have to add 6 days to have the first Monday, 5 for a Wednesday and so on.

What I did was to first have the first day of the year, which in our gregorian calendar always is January, 1st. I then formatted the date to find which weekday it is and then added a switch to have the offset-value for the first Monday of any given year.

The formula in aforementioned label control turned out as follows:

After now having the different days until the first Monday of the selected year, I modified the item property of dropdown for the week selection as such:

 

What now?

So now I had the offset value for finding the first monday of the year. Next I needed to find the days until the monday of the week I selected in my week selection dropdown.

With the first part of the final formula safely stowed away in my label control, I added another label control to hold the second part of the formula. the one where I would calculate the days from the first day of the year until the monday of the week I selected.

Here I had to account for the difference in weeks of the years as well as add or subtract the offset until the first monday, depending on whether it is a 52 or a 53 week year.

I also noticed, that I had a second offset in days depending on which day a year started. After a bit of testing I noticed that if the given year started with a Thursday, Wednesday or Tuesday I had 2, 4 or 6 days that I was off ( Thursday – 6 days, Wednesday -4 days and Tuesday 2 days – if anyone reading this can give me an explanation for that I’d be ever so happy, because I am sure there has to be one, for now I leave those as ‚magic numbers‘).

After a bit of trial and error and a fair amount of frowning I came up with the following formula:

So here I am checking if the offset until the first Monday of the year is greater than or equal to 4 (since I had that previously mentioned second offset to deal with). If yes I can take the week number, multiply it by 7 (days/week) and then subtract the offset until the first monday and from that result I the subtract the second (‚magic number‘) offset. For all other years (the ones with less than 4 days until the first Monday) I have to subtract one week from my selected week number and then add the days to first Monday offset. The result of this formula is the days from January, 1st of the selected year until the Monday of the selected week.

 

The last part

Now all that was left to do, was putting it into a simple DateAdd function to find the specific date of the selected week’s monday:

Film of the screen

Here are the days shown in the gif. Feel free to validate the results.

 

Tabelle Werte

Thanks for reading and I hope I could help some of you out there!

All three parts together (code snippet)