Calculating week numbers

(0) Share

Report

Posted on by Gyllentid

894

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 🙂

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (2)

Sort by

ccc333ab 1,144 on at

Like
a
(0)

Report
Copy link

Link copied!

@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:

Was this reply helpful? Yes No
Gyllentid 894 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

ccc333ab 1,144 on at

Like
a
(1)

Report
Copy link

Link copied!
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).

Was this reply helpful? Yes No
Verified answer

Gyllentid 894 on at

Like
a
(1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No