cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Re: How to calculate week number?

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

Highlighted
Helper II
Helper II

Re: How to calculate week number?

formula not valid according to powerapps

Highlighted
Helper II
Helper II

Re: How to calculate week number?

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

Highlighted
Helper II
Helper II

Re: How to calculate week number?

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)

Highlighted
New Member

Re: How to calculate week number?

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.

Highlighted
Advocate II
Advocate II

Re: How to calculate week number?

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
)

 

Highlighted
Helper II
Helper II

Re: How to calculate week number?

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

Highlighted
Frequent Visitor

Re: How to calculate week number?

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 🙂

Highlighted
Regular Visitor

Re: How to calculate week number?

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)

 

Highlighted
Helper II
Helper II

Re: How to calculate week number?

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

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

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