Convert week number to date

[jvdlinden]

Dear Community Members,

In contrast to all other threads, I would like to convert a week number to a date.

In my scenario, a product will be delivered in a specified week number. I would like to create a set of Microsoft Planner tasks for a specific set of users, but Planner requires an actual date instead of a week number.

Example:

The week number value is registered in the system as "13/2015".

"13" represents the week number, and "2015" represents the year.

Desired outcome:

I would like Power Automate to determine: the actual date of the first Monday of the week entered in the system.

In The Netherlands a week starts at a Monday. The desired outcome would be: "23-03-2015" (format: dd-mm-yyyy).

Would there be a way to achieve this?

Many thanks for all the help provided!

Kind regards,

Joost

[edgonzales]

@jvdlinden 

Hi there.  So, there are probably more elegant ways to do this, but here's what I did:

• Take the input ('13/2015') and split() it on the /.  That gives us the week number and the year in two separate strings.
• Multiply the weeks by '7' to get the number of days into the year, and then add that to 1/1/<year> to get an actual date in the week you want.
• By using dayofWeek(), we can find out how far from Monday we are, and make that a negative number which we then add to the actual date from the step above.  That will give you the traditional week-start of Sunday, so just add one more to get the Monday.  

You can probably combine some of these depending on your comfort level with expressions, but here's a sketch:

Good luck and keep us posted.

-Ed-

PS - If you want me to go into detail on any of the expressions, happy to do so.

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

[jvdlinden]

Dear @edgonzales ,

Thanks a lot for your efforts to help me out with this one.

I have made a test on my side. The result of the test is that the actions are performed properly, but that the outcome is slightly incorrect.

I have added a variable as input: 13/2015

The result is a properly formatted NL date, but it is 30-03-2015

And as you can see in the links below, week 13 of calendar year 2015 started at Monday 23rd of March:

https://www.kalender-365.nl/kalender-2015.html

https://weeknummers.com/kalender/2015/

But somehow the output of the Flow is one week later (and therefore too late). 

Would there be a way to resolve this?

Again, thanks a lot for your help!

Kind regards,

Joost

[v-alzhan-msft]

Hi @jvdlinden ,

Please refer to screenshot below to create the flow:

The expression in the Compose 2 as below:

formatDateTime(addDays(concat(last(split(outputs('Compose'),'/')),'-01-01'),sub(mul(sub(int(first(split(outputs('Compose'),'/'))),1),7),3)),'dd-MM-yyyy')

Best regards,

Alice       

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jvdlinden]

Hi @v-alzhan-msft , Alice,

Thanks for your help as well.

I have created another flow and configured it as you have suggested.

When I use 9/2021 as input (week 9 of 2021 starts at March 1st) it results a completely different date:

The 23rd of February 2021 isn't even a Monday, but a Tuesday in this case, and it is also not in the correct week 😞

[edgonzales]

@jvdlinden 

Hi there.  I changed the input on mine to 9/2021 and ended up with March 1st, so the only thing I can come up with is that NL backend date math is different? or there's something slightly off in the expressions.  Do you want to post the ones from your first flow here and we can compare?

Just a thought.

-Ed-

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

[jvdlinden]

Hi @edgonzales

Correct when I use input 9/2021 it ends up with March 1st for me too.

But 13/2015 as inputs results in output: 30-03-2015 which is incorrect.

Below I have included all the details from your workflow configuration:

Compose WeekToDays

mul(int(split(variables('Input'),'/')[0]),7)

Compose StartOfYear

Concat('1/1/',split(variables('Input'),'/')[1])

Compose GetDateInWeek

formatDateTime(addDays(outputs('Compose_StartOfYear'),outputs('Compose_WeekToDays')))

Compose MakeDayNegative

mul(dayOfWeek(outputs('Compose_GetDateInWeek')),-1)

Compose FinalMonday

addDays(outputs('Compose_GetDateInWeek'),add(outputs('Compose_MakeDayNegative'),1),'dd-MM-yyyy')

[edgonzales]

Ok, I'm sure I'm missing something simple in my original math, but I think this will fix the issue.  Change the final Monday expression to this:

addDays(outputs('Get_date_in_week'),add(outputs('MakeDayNegative'),-6),'dd-MM-yyyy')

Which will get the Monday previous from my calculated date (which, for some reason, is always in the week following the one you want).

Keep us posted.

-Ed-

[jvdlinden]

Hi @edgonzales I think we are almost there.

I have configured the flow exactly as you have requested.

Next, I took a few weeks across 2015 and 2016 to determine if it would result the correct date.

Purposely I chose week 22 of 2016 because in the middle of this week May was ending. 

So, input string: 22/2016

Somehow, for this week the output was 23-05-2016 where it actually was 30-05-2016 in real life.

Could you shed some light on this one as well?

Many thanks!

[edgonzales]

@jvdlinden 

Maybe I'm misunderstanding the scope.  On my calendar, May 23rd is the Monday for Week 22 in 2016.

Does your calendar show something different?

[jvdlinden]

@edgonzales  well I have just learned something interesting.

This Dutch Wikipedia article describes how the week number is being calculated: https://nl.wikipedia.org/wiki/Weeknummer

when you translate it to English using i.e. Chrome you might understand.

It says: "according to the international ISO8601 (https://nl.wikipedia.org/wiki/ISO_8601), the first week of a year will be the week that has 4 or more days of that calendar year in it.". Honestly I didn't even knew...

Examples:

2020 -> starts at Wednesday January 1st. Sunday is considered as the last day of the week. Sunday was the 5th of January. Meaning there would be five days in the first week, this means the week of January 1st falls under week number 1 of calendar year 2020.

2021 however -> starts at Friday January 1st. Sunday is considered as the last day of the week. Sunday will be the 3rd of January. Meaning there would be three days in the first week, this means the week January 1st falls under week number 53 of calendar year 2020.

Might this be related to the problem/behavior?

[edgonzales]

@jvdlinden 

What a mess.  It makes sense, though.

But yes, that would absolutely explain the wonky results.  One way to verify would be to choose a date in a year that started on a Sunday or Monday to see if our new formula returns the prior week.

Are you using Power BI?  I know there's a way in there to easily create a date table that would basically list every date in a range (and going forward) along with whatever components you add (like week number).  Maybe then we could do some kind of a lookup.  It would be an extra step or two, but the results would at least be consistent and accurate.

Good find!

-Ed

[jvdlinden]

For now I ended up creating a SharePoint list with two columns:

- Title (single line of text), containing the 13/2020 week number

- Date (date only), containing the first day of that week

Unfortunate, but for now the quickest way to achieve my goal.