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
Ryball
Frequent Visitor

How to calculate week number?

‎01-30-2017 02:59 AM

Im creating a small booking app, where the users are used to Weeknumbers.

 

Is it posible to spesify like :

 

Week : 5  

Result would be : 30/1-2017 - 5/2-2017

 

Or vice versa,

Spesify the date and then get the current weeknumber.

 

This allso has to adjust for leap year.

 

Best Regards

Raymond

Message 1 of 41
30,141 Views
40 REPLIES 40
WarrenH
New Member
In response to jeffsimons

‎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 41
1,641 Views
0 Kudos
Mark2457
Helper II
Helper II
In response to Anonymous

‎11-19-2019 06:27 AM

formula not valid according to powerapps

Message 22 of 41
1,594 Views
0 Kudos
Mark2457
Helper II
Helper II
In response to jeffsimons

‎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 41
1,590 Views
0 Kudos
Mark2457
Helper II
Helper II
In response to AUPowerappps

‎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 41
1,588 Views
0 Kudos
UniPer
New Member
In response to Mark2457

‎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 41
1,565 Views
Fleur
Advocate II
Advocate II
In response to AUPowerappps

‎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 41
1,498 Views
Mark2457
Helper II
Helper II
In response to Fleur

‎12-17-2019 04:28 AM

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

Message 27 of 41
1,491 Views
0 Kudos
jadenpatel
Frequent Visitor
In response to AUPowerappps

‎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 41
1,113 Views
jeffwllms
Frequent Visitor

‎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 41
1,085 Views
RMC-Pro
Helper II
Helper II
In response to jeffwllms

‎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 41
997 Views
0 Kudos

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

View All
Top Solution Authors
View All
Users online (3,000)