Calculating ISO 8601 Week Number for Dates in Power Automate

An issue that a lot of users have faced with Power Automate is the inability to find out the ISO 8601 Week Number for a given date; this was raised as an idea in the Power Apps Ideas forum back in 2017, and you can find numerous questions about it on the Power Automate forums, but a lot of the answers I’ve seen are incomplete or are overly complex.

If I was using Excel, I could find out the week number for a date using the ISOWEEKNUM function, so it’s a bit frustrating that I can’t do it this easily in Power Automate. I’m not the kind of person to back down from a challenge though, so I decided to see if I could figure out an easy solution to this problem.

The SOlution

There are a couple of things to be aware of when trying to work out the ISO Week Number:

1. An ISO week-numbering year will have either 52 or 53 full weeks
2. The ISO weeks always start on a Monday
3. The first ISO week of a year is the first week that contains the first Thursday of the Gregorian calendar

The Wikipedia article on ISO Week Dates has some great information if you’re not familiar with it.

Now that we know the key elements of ISO Week Dates, we can get our thinking caps on. I went through a lot of trial and error to work this out, but what I’ve ended up with below is a solution that I’ve tested successfully against over 1,000 randomly generated dates and it has been 100% correct.

1. Manually trigger a flow – to demonstrate the Flow I’m just using a manual trigger with a Date input

2. Compose Start of Week (Monday) – as we know that ISO weeks always start on a Monday, we want to work out the Monday of the week our trigger date is in. Power Automate numbers days of the week beginning from Sunday, where Sunday is 0, Monday is 1, etc. with the dayofweek expression.

To calculate Monday we subtract the dayofweek value of our trigger date minus 1 to get a total amount of days to subtract. As we know a Sunday will be a 0, we also add in an IF condition to set the value to subtract to 6 if the day is a Sunday.

To give an example of this:

Date = 2020-04-29

Dayofweek for Date = 3

3 – 1 = 2

Monday = 2020-04-29 minus 2 days = 2020-04-27

The expression is constructed as follows:

subtractFromTime(triggerBody()['date'], if(equals(dayofweek(triggerBody()['date']),0),6,sub(dayofweek(triggerBody()['date']),1)), 'Day')

3. Compose Thursday – now that we have the Monday value, we want to find out the value for the Thursday in the week of our triggering date. We do this as the first ISO week of the year will always have a Thursday in it, and therefore it’s best to set our calculations from this day. Thursday is always 3 days after Monday, so we just add 3 days to the Monday value with the expression below:

addDays(outputs('Start_of_Week_(Monday)'),3)

4. Compose ISO Week Number – To calculate the week number, we need to first find out what day of the year the Thursday is on, and we can do that with the dayofyear expression. You would think that you could just divide the value by 7 and get the week number, but its not that simple.

In 2020, the first Thursday was 2nd January which is Day 2 of the year. the integer value of 2/7 is 0 (in integer maths everything after the decimal point is ignored). ISO Week Numbers start at 1, so 0 is an invalid value. You might think we could just add 1 to this value, but this doesn’t work either.

The 1st Thursday of 2016 was January 7th (7th day of the year). The integer value of 7/7 is 1, and if we added 1 to this it would give us a first week of the year as 2, which obviously isn’t correct.

The simplest way to avoid this is to add 6 days to the DayofYear value for our Thursday, then divide the result by 7 to get our integer value.

To see this in action, and continuing our example from above:

The expression is written as:

div(add(dayofyear(outputs('Thursday')),6),7)

5. Respond to a PowerApp or Flow – the final step in this Flow is to set a response action with 2 outputs:

1. Input date – the value that we input to the Flow
2. Week Number – we will set this as follows:

Week @{outputs('ISO_Week_Number')} of @{formatdatetime(triggerBody()['date'],'yyyy')}

Conclusion

With this Flow I wanted to create a simple solution to calculate the ISO Week Number for any given date, without lots of nested conditions and weird workarounds. I presented it above as a set of 3 compose actions which is easier for debugging and writing the formula, but you could also have the whole expression in a single compose action as below:

div(add(dayofyear(addDays(subtractFromTime(triggerBody()['date'], if(equals(dayofweek(triggerBody()['date']),0),6,sub(dayofweek(triggerBody()['date']),1)), 'Day'),3)),6),7)

If you find this useful, then I’d love to hear from you, so please leave a comment below or contact me on Twitter or LinkedIn.

Original Post https://ryanmaclean365.com/2020/04/29/calculating-iso-8601-week-number-for-dates-in-power-automate/