Calculating week numbers

(0) Share

Report

Posted on by Gyllentid

892

I am not getting it right with the week numbers in Power Automate. I tried...

add(div(dayOfYear(outputs('Date')),7),1) - it gives week 31 for Friday 29th of July 2022. It's week 30. It works for 2020.

div(dayOfYear(outputs('Date')),7) - it gives week week 30 for Wednesday 29th of July 2020. It's week 31. It works for 2022.

In another thread I found a calculation to find weeks between dates from @ccc333ab. This has worked for all years I have tried:

add(div(div(sub(ticks(outputs('Date')),ticks(formatDateTime(outputs('FirstDateOfYear'),'yyyy-MM-dd'))),864000000000),7),1)

For the FirstDateOfYear:

concat(formatDateTime(outputs('Date'),'yyyy'),'-01-01')

If you need to find the Monday of the Date you can use this formula in the compose of Date:

subtractFromTime(outputs('ActualDate'), if(equals(dayofweek(utcNow()),0),6,sub(dayofweek(outputs('ActualDate')),1)), 'Day')

Grateful for this community 🙂

All responses (4)

Answers (2)

Verified answer

Gyllentid 892 on at

Like (1)

Report

Re: Calculating week numbers

That's great, I actually found your other calculation working fine that you made in another thread:add(div(div(sub(ticks(outputs('Date')),ticks(formatDateTime(outputs('FirstDateOfYear'),'yyyy-MM-dd'))),864000000000),7),1)

But this new one might be more wholesome 😊

@ccc333ab
Verified answer

ccc333ab 1,144 on at

Like (1)

Report
Re: Calculating week numbers

Ahh...OK.

My Calendar is based on the US settings which starts the year on the first day of the year regardless if its the start of the week or not. Now based on ISO, I believe it sets week 1 of a year based on a week at least having 4 days of the following year. So based on that I created the following formula.

if(greater(dayofweek(concat(formatDateTime(triggerBody()['date'],'yyyy'),'-01-01')),3),div(dayofyear(triggerBody()['date']),7),add(div(dayofyear(triggerBody()['date']),7),1))

I'm sure there is a more eloquent way of doing this but this:
Four days require at least starting on Thurs (if your week starts on a Monday, I believe ISO starts on a Monday)
So I check to see what day of the week the first day of the year is
If its greater than Wednesday (day of week #3), don't add 1 to the calculation
If its before Wednesday, add 1 to the calculation
When I do this it seems to work (haven't done full testing on more than a few dates).
Gyllentid 892 on at

Like (0)

Report

Re: Calculating week numbers

My outlook says this @ccc333ab :

Maybe you have another kind of calculation on your calendar? Here is a link using iso week numbers.

You must have misunderstood, I didn't mention what the first formula produced for 2020. The first formula using add +1 is working well for 2020. It gives week 31. So to clarify, add(div(dayOfYear(outputs('Date')),7),1) works well for 2020.
ccc333ab 1,144 on at

Like (0)

Report

Re: Calculating week numbers

@shavora I think your formula is correct.

When using your exact formula I actually get the result of:
July 22, 2022 = Week 31
July 29, 2020 = Week 31 (you say you are getting Week 30)

Now when I look in Outlook at the week numbers it says:
July 29, 2022 = Week 31 (you say it should be Week 30)
July 29, 2020 = Week 31 (you say it should be week 30)

And from outlook: July 29, 2020:
And July 29, 2022: