cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
WarrenH
New Member

Re: How to calculate week number?

‎10-24-2019 04:56 AM

Excel: Calculating Network Rail Week

If A1 is a date, put the following in A2 to show the rail week number:

=INT(WEEKNUM(A1+637,16))

This supports those annoying 53 week years

Message 21 of 32
979 Views
0 Kudos
Reply
Highlighted
Mark2457
Helper II
Helper II

Re: How to calculate week number?

‎11-19-2019 06:27 AM

formula not valid according to powerapps

Message 22 of 32
931 Views
0 Kudos
Reply
Highlighted
Mark2457
Helper II
Helper II

Re: How to calculate week number?

‎11-19-2019 07:17 AM

Close, except 2 things: it returns a negative (no probles: just prefix with minus sign)

 

Doesn't seem to work for years that have 53 weeks (e.g. 2020-12-27 to 2021-01-02 is week 53)

 

Start day Monday

-If(DateDiff(Date(2020,12,31),Date(Year(Date(2020,12,31)),1,1),Days)>0,RoundDown((DateDiff(Date(Year(Date(2020,12,31))-1,1,1),Date(2020,12,31),Days)+ (8-Weekday(Date(Year(Date(2020,12,31)),1,1),Monday)))/7,0),RoundDown((DateDiff(Date(2020,12,31),Date(Year(Date(2020,12,31)),1,1),Days)+ (8-Weekday(Date(Year(Date(2020,12,31)),1,1),Monday)))/7,0))+1

 

Start day Sunday

 


-If(DateDiff(Date(2020,12,31),Date(Year(Date(2020,12,31)),1,1),Days)>0,RoundDown((DateDiff(Date(Year(Date(2020,12,31))-1,1,1),Date(2020,12,31),Days)+ (8-Weekday(Date(Year(Date(2020,12,31)),1,1),Sunday)))/7,0),RoundDown((DateDiff(Date(2020,12,31),Date(Year(Date(2020,12,31)),1,1),Days)+ (8-Weekday(Date(Year(Date(2020,12,31)),1,1),Sunday)))/7,0))+1

Message 23 of 32
928 Views
0 Kudos
Reply
Highlighted
Mark2457
Helper II
Helper II

Re: How to calculate week number?

‎11-19-2019 07:49 AM

AUPowerApps is close (except for syntax errors), but seems to be incorrect for Dec 22nd 2019 (see "ERROR"). This is a Sunday and should be week 52. It could just be that the formula below is for Mon being start of week. I need Sun to be start of week 

 

"Today = Week: " &

RoundDown((RoundUp(Today()-Date(Year(Today()-Weekday(Today()-1)+4),1,3),0)+
Weekday(Date(Year(Today()-Weekday(Today()-1)+4),1,3))+5)
/7,0)

& Char(10) & "2019-01-01 = Week: " &

RoundDown((RoundUp(Date(2019,01,01)-Date(Year(Date(2019,01,01)-Weekday(Date(2019,01,01)-1)+4),1,3),0)+
Weekday(Date(Year(Date(2019,01,01)-Weekday(Date(2019,01,01)-1)+4),1,3))+5)
/7,0)


& Char(10) & "2019-12-22 = Week: " &

RoundDown((RoundUp(Date(2019,12,22)-Date(Year(Date(2019,12,22)-Weekday(Date(2019,12,22)-1)+4),1,3),0)+
Weekday(Date(Year(Date(2019,12,22)-Weekday(Date(2019,12,22)-1)+4),1,3))+5)
/7,0) & " - ERROR"

 


& Char(10) & "2019-12-23 = Week: " &

RoundDown((RoundUp(Date(2019,12,23)-Date(Year(Date(2019,12,23)-Weekday(Date(2019,12,23)-1)+5),1,3),0)+
Weekday(Date(Year(Date(2019,12,23)-Weekday(Date(2019,12,23)+1)+4),1,3))+5)
/7,0)


& Char(10) & "2019-12-31 = Week: " &

RoundDown((RoundUp(Date(2019,12,31)-Date(Year(Date(2019,12,31)-Weekday(Date(2019,12,31)-1)+4),1,3),0)+
Weekday(Date(Year(Date(2019,12,31)-Weekday(Date(2019,12,31)-1)+4),1,3))+5)
/7,0)

& Char(10) & "2020-01-01 = Week: " &

RoundDown((RoundUp(Date(2020,01,01)-Date(Year(Date(2020,01,01)-Weekday(Date(2020,01,01)-1)+4),1,3),0)+
Weekday(Date(Year(Date(2020,01,01)-Weekday(Date(2020,01,01)-1)+4),1,3))+5)
/7,0)

& Char(10) & "2020-12-31 = Week: " &

RoundDown((RoundUp(Date(2020,12,31)-Date(Year(Date(2020,12,31)-Weekday(Date(2020,12,31)-1)+4),1,3),0)+
Weekday(Date(Year(Date(2020,12,31)-Weekday(Date(2020,12,31)-1)+4),1,3))+5)
/7,0)

 

I think this is correct if you want Sun to be start of week

 

"Today = Week: " &

RoundDown((RoundUp(Today()-Date(Year(Today()-Weekday(Today()-1)+4),1,3),0)+
Weekday(Date(Year(Today()-Weekday(Today()-1)+4),1,3))+6)
/7,0)

& Char(10) & "2019-01-01 = Week: " &

RoundDown((RoundUp(Date(2019,01,01)-Date(Year(Date(2019,01,01)-Weekday(Date(2019,01,01)-1)+4),1,3),0)+
Weekday(Date(Year(Date(2019,01,01)-Weekday(Date(2019,01,01)-1)+4),1,3))+6)
/7,0)


& Char(10) & "2019-12-22 = Week: " &

RoundDown((RoundUp(Date(2019,12,22)-Date(Year(Date(2019,12,22)-Weekday(Date(2019,12,22)-1)+4),1,3),0)+
Weekday(Date(Year(Date(2019,12,22)-Weekday(Date(2019,12,22)-1)+4),1,3))+6)
/7,0) & " - ERROR"

 


& Char(10) & "2019-12-23 = Week: " &

RoundDown((RoundUp(Date(2019,12,23)-Date(Year(Date(2019,12,23)-Weekday(Date(2019,12,23)-1)+4),1,3),0)+
Weekday(Date(Year(Date(2019,12,23)-Weekday(Date(2019,12,23)+1)+4),1,3))+6)
/7,0)


& Char(10) & "2019-12-31 = Week: " &

RoundDown((RoundUp(Date(2019,12,31)-Date(Year(Date(2019,12,31)-Weekday(Date(2019,12,31)-1)+4),1,3),0)+
Weekday(Date(Year(Date(2019,12,31)-Weekday(Date(2019,12,31)-1)+4),1,3))+6)
/7,0)

& Char(10) & "2020-01-01 = Week: " &

RoundDown((RoundUp(Date(2020,01,01)-Date(Year(Date(2020,01,01)-Weekday(Date(2020,01,01)-1)+4),1,3),0)+
Weekday(Date(Year(Date(2020,01,01)-Weekday(Date(2020,01,01)-1)+4),1,3))+6)
/7,0)

& Char(10) & "2020-12-31 = Week: " &

RoundDown((RoundUp(Date(2020,12,31)-Date(Year(Date(2020,12,31)-Weekday(Date(2020,12,31)-1)+4),1,3),0)+
Weekday(Date(Year(Date(2020,12,31)-Weekday(Date(2020,12,31)-1)+4),1,3))+6)
/7,0)

Message 24 of 32
925 Views
0 Kudos
Reply
Highlighted
UniPer
New Member

Re: How to calculate week number?

‎11-23-2019 03:55 AM
I think I've managed to finish the script where the following cases are covered:
  • Week starts on Monday;
  • Leap years starting on wednesday or thursday have 53 weeks;
  • Non leap years starting on thursday have 53 weeks;
  • Last week of the year has only 3 days (becomes week 1);
  • First week of next year has only 3 days (becomes weeknumber 52 or 53, depending on last week of the year before).

It was a hell of a job but I didn't found any working solution for the variables described above:

 

 

"Selected Week: " & 
If(
    Or(
        And(
            Year(DatePicker_tr.SelectedDate) / 4 - Round(Year(DatePicker_tr.SelectedDate) / 4;0) = 0;
            Weekday(Date(Year(DatePicker_tr.SelectedDate);Month(1);Day(1))) = 4
        );
        
        Weekday(Date(Year(DatePicker_tr.SelectedDate);Month(1);Day(1))) = 5
    );

    RoundDown((DatePicker_tr.SelectedDate - Date(Year(DatePicker_tr.SelectedDate);1;1) + (Weekday(Date(Year(DatePicker_tr.SelectedDate);1;1) - 1;Monday))) / 7;0)+1; // Makes 53 weeks in a year
        
    If(
        And(
            Or(
                Weekday(Date(Year(DatePicker_tr.SelectedDate);12;31)) = 2;
                Weekday(Date(Year(DatePicker_tr.SelectedDate);12;31)) = 3;
                Weekday(Date(Year(DatePicker_tr.SelectedDate);12;31)) = 4
            );

            RoundDown((DatePicker_tr.SelectedDate - Date(Year(DatePicker_tr.SelectedDate);1;1) + (Weekday(Date(Year(DatePicker_tr.SelectedDate);1;1) - 1;Monday))) / 7;0)=53

        );
                
    RoundDown((DatePicker_tr.SelectedDate - Date(Year(DatePicker_tr.SelectedDate);1;1) + (Weekday(Date(Year(DatePicker_tr.SelectedDate);1;1) - 1;Monday))) / 7;0)-52;
            
    If (
        And(
            Or(
                Weekday(Date(Year(DatePicker_tr.SelectedDate);12;31)) = 2;
                Weekday(Date(Year(DatePicker_tr.SelectedDate);12;31)) = 3;
                Weekday(Date(Year(DatePicker_tr.SelectedDate);12;31)) = 4
            );

            RoundDown((DatePicker_tr.SelectedDate - Date(Year(DatePicker_tr.SelectedDate);1;1) + (Weekday(Date(Year(DatePicker_tr.SelectedDate);1;1) - 1;Monday))) / 7;0)=52

            );
        RoundDown((DatePicker_tr.SelectedDate - Date(Year(DatePicker_tr.SelectedDate);1;1) + (Weekday(Date(Year(DatePicker_tr.SelectedDate);1;1) - 1;Monday))) / 7;0)-51;

        If(
            And(
                Or(
                    Weekday(Date(Year(DatePicker_tr.SelectedDate);1;1)) = 6;
                    Weekday(Date(Year(DatePicker_tr.SelectedDate);1;1)) = 7;
                    Weekday(Date(Year(DatePicker_tr.SelectedDate);1;1)) = 1
                );
                Or(
                    And(
                        Year(DatePicker_tr.SelectedDate-1) / 4 - Round(Year(DatePicker_tr.SelectedDate-1) / 4;0) = 0;
                        Weekday(Date(Year(DatePicker_tr.SelectedDate-1);Month(1);Day(1))) = 4
                    );
                    Weekday(Date(Year(DatePicker_tr.SelectedDate-1);Month(1);Day(1))) = 5
                );
                RoundDown((DatePicker_tr.SelectedDate - Date(Year(DatePicker_tr.SelectedDate);1;1) + (Weekday(Date(Year(DatePicker_tr.SelectedDate);1;1) - 1;Monday))) / 7;0)=0
            );
            RoundDown((DatePicker_tr.SelectedDate - Date(Year(DatePicker_tr.SelectedDate);1;1) + (Weekday(Date(Year(DatePicker_tr.SelectedDate);1;1) - 1;Monday))) / 7;0)+53;
            If(
                And(
                    Or(
                        Weekday(Date(Year(DatePicker_tr.SelectedDate);1;1)) = 6;
                        Weekday(Date(Year(DatePicker_tr.SelectedDate);1;1)) = 7;
                        Weekday(Date(Year(DatePicker_tr.SelectedDate);1;1)) = 1
                    );

                    RoundDown((DatePicker_tr.SelectedDate - Date(Year(DatePicker_tr.SelectedDate);1;1) + (Weekday(Date(Year(DatePicker_tr.SelectedDate);1;1) - 1;Monday))) / 7;0)=0);
                    RoundDown((DatePicker_tr.SelectedDate - Date(Year(DatePicker_tr.SelectedDate);1;1) + (Weekday(Date(Year(DatePicker_tr.SelectedDate);1;1) - 1;Monday))) / 7;0)+52;
                    If (
                        RoundDown((DatePicker_tr.SelectedDate - Date(Year(DatePicker_tr.SelectedDate);1;1) + (Weekday(Date(Year(DatePicker_tr.SelectedDate);1;1) - 1;Monday))) / 7;0)=0;
                        RoundDown((DatePicker_tr.SelectedDate - Date(Year(DatePicker_tr.SelectedDate);1;1) + (Weekday(Date(Year(DatePicker_tr.SelectedDate);1;1) - 1;Monday))) / 7;0)+1;
                        If (
                            And(
                                Or(
                                    Weekday(Date(Year(DatePicker_tr.SelectedDate-1);12;31)) = 2;
                                    Weekday(Date(Year(DatePicker_tr.SelectedDate-1);12;31)) = 3;
                                    Weekday(Date(Year(DatePicker_tr.SelectedDate-1);12;31)) = 4
                                );
                                RoundDown((Date(Year(DatePicker_tr.SelectedDate-1);12;31) - Date(Year(DatePicker_tr.SelectedDate-1);1;1) + (Weekday(Date(Year(DatePicker_tr.SelectedDate-1);1;1) - 1;Monday))) / 7;0)=52
                            );
                            RoundDown((DatePicker_tr.SelectedDate - Date(Year(DatePicker_tr.SelectedDate);1;1) + (Weekday(Date(Year(DatePicker_tr.SelectedDate);1;1) - 1;Monday))) / 7;0)+1;
                            RoundDown((DatePicker_tr.SelectedDate - Date(Year(DatePicker_tr.SelectedDate);1;1) + (Weekday(Date(Year(DatePicker_tr.SelectedDate);1;1) - 1;Monday))) / 7;0)
                        )
                    )
                )
            )
        )
    )
)

 

 

In the solution above, the weeknumber is based on the date selected in 'DatePicker_tr', replace that with any source you like. I hope more people can benefit.

Message 25 of 32
902 Views
Reply
Highlighted
Fleur
Advocate II
Advocate II

Re: How to calculate week number?

‎12-16-2019 03:56 PM

I had to replace some semicolons with commas - but then it worked great.  Thank you!

RoundDown(
(RoundUp (
selec.SelectedDate - Date(
Year(selec.SelectedDate - Weekday(selec.SelectedDate - 1) + 4),
1,
3
),
0
) + Weekday(
Date(
Year(selec.SelectedDate - Weekday(selec.SelectedDate - 1) + 4),
1,
3
)
) + 5) / 7,
0
)

 

Message 26 of 32
835 Views
Reply
Highlighted
Mark2457
Helper II
Helper II

Re: How to calculate week number?

‎12-17-2019 04:28 AM

So it looks like this forum is instering semi-colons in place of commas

Message 27 of 32
827 Views
0 Kudos
Reply
Highlighted
jadenpatel
Frequent Visitor

Re: How to calculate week number?

‎05-26-2020 01:04 AM

If you wanted to get the same week number in flow:

 

Create a string variable, (I called mine WeekNumCalc)

 

formatDateTime(addDays(addDays(utcNow(), mul(add(dayOfWeek(addDays(utcNow(),-1)),1),-1)),4),'yyyy') -01-03

 

WeekNumCalc variable valueWeekNumCalc variable value

 
 

Create a integer variable, (I Called mine WeekNum)


div(add(add(div(sub(ticks(utcNow()),ticks(variables('WeekNumCalc'))),864000000000),add(dayOfWeek(variables('WeekNumCalc')),1)),5),7)

 

^You can also change utcNow() so that you can get the week number for a future date too 🙂

Message 28 of 32
449 Views
Reply
Highlighted
jeffwllms
Regular Visitor

Re: How to calculate week number?

‎05-28-2020 11:30 AM

I know this post is a little old now.  I thought I would share the solution I came up with in case it might still help someone.  I did some testing, it should be working for all dates after Jan 1 1900 (can edit to work with dates prior), works on leap years and years with years that start on thursday (53 weeks).

This is based on ISO week format:  Mondays start, week number calculated based on were the Thursday of the given week falls. 

With some altering I think it could work for other Sunday or a different start of the week.  I think it could likely be optimized a little more but I am just starting with powerapps and I am not familiar with all the functions available

Formula uses date supplied by "DatePicker1.SelectedDate"

 

 

 

RoundDown((DateDiff(Date(Year(DateAdd(Date(1900,1,1),RoundDown(DateDiff(Date(1900,1,1),DatePicker1.SelectedDate,Days)/7,0)*7+3,Days))-1,12,31),DateAdd(Date(1900,1,1),RoundDown(DateDiff(Date(1900,1,1),DatePicker1.SelectedDate,Days)/7,0)*7+3,Days),Days) + 6)/7,0)

 

 

 

--UPDATE--

Spent a little time trying to simplify the formula.  Came up with the following.  It should now work for any date (not just ones after 1/1/1900 like the above formula).  Still works on the years that have 53 weeks.  Still a Monday start but can easily be adjusted.

 

 

RoundDown((DateDiff(Date(Year(DateAdd(DatePicker1.SelectedDate,4 - Weekday(DatePicker1.SelectedDate,Monday),Days))-1,12,31),DateAdd(DatePicker1.SelectedDate,4 - Weekday(DatePicker1.SelectedDate,Monday),Days),Days)+6)/7,0)

 

Message 29 of 32
421 Views
Reply
Highlighted
RMC-Pro
Helper II
Helper II

Re: How to calculate week number?

‎06-18-2020 06:36 AM

Great  @jeffwllms ,I am using it.

 

You maybe have an add-on to your formula, how to calculate starting (Monday) and ending (Sunday) date based on a Week No?

Tnxs

br

RMC-Pro

Message 30 of 32
333 Views
0 Kudos
Reply

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

View All
Top Solution Authors
View All
Top Kudoed Authors
View All
Users online (6,659)