Unanswered

Calculate American Week Number in Power Automate

(0) Share

Report

Posted on by beeepaiva

I'm trying to build a flow that calculates the week number of the current date, but in american format that the week starts on Sunday, every topic or tutorial that I've found is using Monday as the first day, according to ISO calculating, so the number is -1 than the current week and implement an add(FORMULA, 1) is no feaseble cause maybe in some year it won't work...

Does anyone know a workaround to work with WEEK NUM not in ISO mode?

The flow is schedule to run every Sunday:

I've tried some formulas to do this calculation:

div(add(dayofyear(addDays(subtractFromTime(utcNow(), if(equals(dayofweek(utcNow()),0),1,sub(dayofweek(utcNow()),6)), 'Day'),3)),6),7)

div(add(dayofyear(addDays(subtractFromTime(utcNow(), if(equals(dayofweek(utcNow()),0),6,sub(dayofweek(utcNow()),1)), 'Day'),3)),6),7)

add(div(dayOfMonth(utcNow()),7),1)

But always returns the week according to ISO and not American one

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (0)

ryleybauer 327 on at

Like (0)

Report
I thought the dayOfWeek function starts on Sunday? Isn't Sunday 0 and Saturday 6?
WeekNum also has a second parameter to specify which day begins a week

Did you try:
add(div(dayOfYear(convertFromUtc(utcNow(), 'TIME ZONE', 'U')),7),1)

WeekNum([DATETIME], StartOfWeek.Sunday)
Please mark as solution if this helped solve your issue.

Was this reply helpful? Yes No
beeepaiva 17 on at

Like (1)

Report

@ryleybauer, thanks for answering, but it didn't work 😞

Power Automate doesn't have the WeekNum formula.
As a test I'm using the timestamp date as 2022-06-26T04:00:00 that must return week 27 cause it was a Sunday and the beggining of 27 week as American Week Calculator as documented here https://www.calconi.com/en/week_numbers/info/week_number_27.php

Was this reply helpful? Yes No
ryleybauer 327 on at

Like (0)

Report

Sorry @beeepaiva, I was thinking about WeekNum in Power Apps and Excel! lol

This is a long way around it, but you can count the number of Sundays/Week start days using the following steps, which accomplishes your goal:
Initialize variables for start day, now date=utcnow, index start date=(YYYY-01-01), and Weeks=1.
Create a Do Until loop using "Index Date" is greater than or equal to "Now date"
Condition: IF dayOfWeek('Index Date')) is equal to "Start day"
YES: increment weeks+1 and set "index date" to next "Start day"
NO: increment "index date" +1
Then, the "weeks" variable will be the American week numbers from this flow. You don't need the compose step, I just used it to check the result in the stand alone flow.

---------------------------------------------
I'm glad to offer assistance. If I've answered your question, please mark my reply as the solution (and thumbs up if you're feeling generous!)
If you like my response, please give it a thumbs up!

Was this reply helpful? Yes No
SL-11020816-0 23 on at

Like (0)

Report

PowerAutomate have "dayOfWeek(timestamp: string)" and "dayOfYear(timestamp: string)" not WEEKNUM. And dayOfWeek start with Sunday as 0.

Was this reply helpful? Yes No