cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
WarrenH
Level: Power Up

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

Mark2457
Level: Powered On

Re: How to calculate week number?

formula not valid according to powerapps

Mark2457
Level: Powered On

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

Mark2457
Level: Powered On

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)

UniPer
Level: Power Up

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.

Fleur
Level: Powered On

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
)

 

Mark2457
Level: Powered On

Re: How to calculate week number?

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

Helpful resources

Announcements
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (4,486)