cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shavora
Super User
Super User

Calculating week numbers

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 🙂

2 ACCEPTED SOLUTIONS

Accepted Solutions
ccc333ab
Solution Sage
Solution Sage

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). 

 

View solution in original post

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

View solution in original post

4 REPLIES 4
ccc333ab
Solution Sage
Solution Sage

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

 

ccc333ab_0-1660146607387.pngccc333ab_1-1660146653607.pngccc333ab_2-1660146695182.png

And from outlook: July 29, 2020: 

ccc333ab_3-1660146787375.png

And July 29, 2022:

ccc333ab_4-1660146857024.png

 

My outlook says this @ccc333ab : 

Screenshot 2022-08-11 at 14.18.13.png

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
Solution Sage
Solution Sage

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). 

 

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

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events happening this month - don't miss out!

 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (2,278)