How to calculate week number?

[WarrenH]

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]

formula not valid according to powerapps

[Mark2457]

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]

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]

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]

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]

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