GET WEEK NUMBER FROM CURRENT DATE

[nagaraj007]

Hello All,

I would want some help to get the week number of current date. I have added the below function to get the current date. If some one could help me to get the week number it would be of great help.

convertFromUtc(utcNow(),'India Standard Time','dd/MM/yyyy')

[Pstork1]

Try this formula

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

[CFernandes]

@nagaraj007 

@Pstork1  formula works 🙂. 

You can also find some more ideas in the comments for the thread, https://powerusers.microsoft.com/t5/Power-Automate-Ideas/How-to-calculate-week-number-in-a-year-in-f... 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[nagaraj007]

Will it work for all years? As I saw in one of the forums the below function works only for 2019. Please confirm. @Pstork1 

[Pstork1]

The function should work for any year.  It is currently based on UTC.  If you want it for India time zone then use this one.

add(div(dayOfYear(convertFromUtc(utcNow(),'India Standard Time','U')),7),1)

[Amanthaper]

edited -

[PedrohMoreira]

Does this also work on Power Automate Desktop?

[Pstork1]

Power Automate Desktop doesn't use the same functions as Power Automate, so no I don't think it will work.

[OliverR-82]

It doesn't seem to work in 2021. It returns '21' for me today while we're currently in week 20, according to my calendar. I suppose I could replace the '1' in the formula with '2' but then it will likely not work correctly for next year, I would expect?

EDIT: or it could be because my calendar uses the ISO week number, which always starts on a Monday. That would explain. Unfortunately, that's the one I need. I'll try the method described here. It's more complex but if it does the job, then great.

[ENRM]

@nagaraj007 

Use the WeekNum and ISOWeekNum functions to determine the week number of a date.

These functions differ in how they determine the first week of the year (week 1):

• WeekNum uses the week containing January 1 as the first week of the year. The result from this function can range from 1 to 54.

• ISOWeekNum uses the week containing the first Thursday of the year as the first week of the year. This follows the ISO 8601 date and time standard definition for week numbering. The result from this function can range from 1 to 53. It is possible that 52 or 53 may be returned for the first days of January since the dates could belong to the last week of the previous year.

Use the second parameter to WeekNum to specify which day begins a week. You can provide either an Excel code number or use the StartOfWeek enumeration:

1, 17	StartOfWeek.Sunday	Week begins on Sunday. Default.
2, 11	StartOfWeek.Monday	Week begins on Monday.
12	StartOfWeek.Tuesday	Week begins on Tuesday.
13	StartOfWeek.Wednesday	Week begins on Wednesday.
14	StartOfWeek.Thursday	Week begins on Thursday.
15	StartOfWeek.Friday	Week begins on Friday.
16	StartOfWeek.Saturday	Week begins on Saturday.

ISOWeekNum always uses Monday as the start of the week. In Excel, the WeekNum function supports an addition code 21 that is not supported here; use ISOWeekNum instead.

If you pass a single number to these functions, the return value is a single result. If you pass a single-column table that contains numbers, the return value is a single-column table of results, one result for each record in the argument's table. If you have a multi-column table, you can shape it into a single-column table, as working with tables describes.

Syntax

WeekNum(DateTime [, StartOfWeek ])

• DateTime - Required. Date/Time value to operate on.
• StartOfWeek - Optional. Excel code or StartOfWeek enumeration that determines which day the week begins.

ISOWeekNum(DateTime)

• DateTime - Required. Date/Time value to operate on. The week always begins on Monday.

[Anonymous]

Anyone managed to get the ISO week number working?

Would love to see an example.

[Anonymous]

Hi Oliver, have you managed? Using the example in the link I get "the expression is invalid"...

[Anonymous]

Here's how I managed to get ISO weeks

The 3 functions:

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

addDays(outputs('Set_the_Monday'),3)

div(add(dayofyear(outputs('Thursday')),6),7)

[grluquec]

Hi all,

I have the same problem in Power Automate flow to get the Week Number of Year, so this is my solution:

1. Get the year of current date:

varYear = utcNow('yyyy')

2. Get the Day of Week for the first day of the current year. For example Jan 01-2021 was Friday or 5 day of week (0-6 week count):

varFWD = dayOfWeek(concat(variables('varYear'),'-01-01'))

3.  Get the Day of Year for current date. For example, for Nov 25-2021 is 329 day (1-365):

varDY = dayOfYear(utcNow())

4.  Get the Day of Week for current date. For example, for Nov 25-2021 is Thursday or 4:

varDW =dayOfWeek(utcNow())

5. Apply the follow formula to get the Week Number of Year:

varWeekNum = ((varDY - varDW + varFDW - 1) / 7 )+ 1

For example for Nov 25, 2021 is varWeekNum ((329 - 4 + 5 - 1) / 7 ) + 1 = 48

In code is:

varWeekNum = add(div(add(add(add(variables('varDY'), mul(variables('varDW'), -1)),variables('varFWD')), -1),7),1)

Note: you can convert utcNow to your local zone time to avoid any problem with time differences.

I hope this works for you and best luck to all.

[relleK]

This was perfect and much simpler than the other solutions.

I have a table in an excel file in a Sharepoint site.

The first column in the table is the Week Number

Column 2 I have calculate the month from the week number. (For anyone who may find this useful)

=TEXT((MONTH(DATE(YEAR(2021),1,1)+$A2*7-1)*29),"MMMM")

Now I will use the calculation of the ISO week to update a row on a Google Sheet. Power Automate only lets us do 1 row at a time, so this make it update the row designated for the current week.

[greshnak]

add(div(add(dayOfYear(variables('dateToday')),sub(dayOfWeek(concat('1/01/',formatDateTime(variables('dateToday'),'yyyy'))),1)),7),1)

[greshnak]

Or replace the variable dateToday for utcNow()

[kgohar]

Thank you for the formula ! 🙂

[ndabenhle_s]

Hi so I am trying to calculate the number of weeks in a month using power automate, can you please assist me if you can

[Pstork1]

Please post this as a new question.  It will get more notice that way and others will be able to find the answer easier in the future.