cancel
Showing results for
Did you mean:
Level: Powered On

## How to calculate week number?

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

1 ACCEPTED SOLUTION

Accepted Solutions
Level: Powered On

## Re: How to calculate week number?

Weekk number ISO:

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)

selec=datepicker or label

Spoiler
remove ; , if need
26 REPLIES 26 Community Support Team

## Re: How to calculate week number?

Hi @Ryball,

For PowerApps, currenlty I thinks writing the formula for week number with the limited fucntion is not a good practice to handle in Apps.

We may need to connect to a date table (which contains the Week number) from some available data source, then just take a reference in PowerApps.

Such as Excel, there is a function call WeekNum which would do this.

See:WEEKNUM function.

Or we may consider submit this as an idea under PowerApps idea forum.

I will discuss with my colleagues and see if there are any other helpful comments on this.

Regards

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Level: Powered On

## Re: How to calculate week number?

Hi,

Since Formulas in Excel are not supported in OneDrive Excel files, I dont see how this would work (usingExcel as a datasource with WEEKNUM), unless I'm missing something?

Thanks

Highlighted
Level: Powered On

## Re: How to calculate week number?

However,

I believe this works in PowerApps for those that need it, Change all 'Saturday' to your week start day, Change all '4,1' (April 1st - this is not a prank, rail week starts that day) to your year start e.g. '1,1' and change all 'Today()' to the date you wish to calculate for.

If(DateDiff(Today(),Date(Year(Today()),4,1),Days)>0,RoundDown((DateDiff(Date(Year(Today())-1,4,1),Today(),Days)+ (8-Weekday(Date(Year(Today()),4,1),Saturday)))/7,0),RoundDown((DateDiff(Today(),Date(Year(Today()),4,1),Days)+ (8-Weekday(Date(Year(Today()),4,1),Saturday)))/7,0))

Hope it helps

Level: Powered On

## Re: How to calculate week number?

Yours didn't quite work right for me but after a fair but of work I ended up with this....

Txt_WkEnd.Text replaces Today() from your version and I use Monday instead of Sunday but .....

Level: Powered On

## Re: How to calculate week number?

Double Post

Level: Powered On

## Re: How to calculate week number?

yeah come april mine bust thanks for taking the time to get it right, confirmed this is now working for my use case,

Jeff

Level: Powered On

## Re: How to calculate week number?

Thank you Jimbobalero for your formula ! I am trying to get the correct week number considering that my first week of the year should be the first week with 4 days. In excel it is referenced as Weeknum(Date;21), is it the StartofWeek.MondayZero in PowerApps ? I have tried to adapt your formula but I can't get it working right for the moment...

Level: Powered On

## Re: How to calculate week number?

Mabye this will help:

Based on  a date picker (DatePicker1) and a label (Label1):

Label 1/ Text:

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

This starts the week on monday....

Level: Powered On

## Re: How to calculate week number?

ABsolutely none of the suggestions on this page worked for me, my scenario is.

-Week starts on Sunday

-Week1 is the Last Week Of Last Year if more days of that week fall in that year

-Week1 Is the FIrst Week Of This Year if more days of that week fall in this year

My calculation is as below (beware! It's long)

```UpdateContext({EndOfThisYear: Date(Year(Today()),12,31)});
If(And(Weekday(Date(Year(Today()),12,31))>3,Weekday(Date(Year(Today()),12,31))<7),
UpdateContext({EndOfThisYear: Date(Year(Today()),12,31)+(7-Weekday(Date(Year(Today()),12,31)))}));
If(Weekday(Date(Year(Today()),12,31))<4,
UpdateContext({EndOfThisYear: Date(Year(Today()),12,31)-(Weekday(Date(Year(Today()),12,31)))}));

UpdateContext({StartOfThisYear: Date(Year(Today()),1,1)});
If(And(Weekday(Date(Year(Today()),1,1))>3),
UpdateContext({StartOfThisYear: Date(Year(Today()),1,1)+(7-Weekday(Date(Year(Today()),1,1)))+1}));
If(And(Weekday(Date(Year(Today()),1,1))<4,Weekday(Date(Year(Today()),1,1))>1),
UpdateContext({StartOfThisYear: Date(Year(Today()),1,1)-(Weekday(Date(Year(Today()),1,1)))}));

UpdateContext({EndOfNextYear: Date(Year(Today())+1,12,31)});
If(And(Weekday(Date(Year(Today())+1,12,31))>3,Weekday(Date(Year(Today())+1,12,31))<7),
UpdateContext({EndOfNextYear: Date(Year(Today())+1,12,31)+(7-Weekday(Date(Year(Today())+1,12,31)))}));
If(Weekday(Date(Year(Today())+1,12,31))<4,
UpdateContext({EndOfNextYear: Date(Year(Today())+1,12,31)-(Weekday(Date(Year(Today())+1,12,31)))}));

UpdateContext({StartOfNextYear: Date(Year(Today())+1,1,1)});
If(And(Weekday(Date(Year(Today())+1,1,1))>3),
UpdateContext({StartOfNextYear: Date(Year(Today())+1,1,1)+(7-Weekday(Date(Year(Today())+1,1,1)))+1}));
If(And(Weekday(Date(Year(Today())+1,1,1))<4,Weekday(Date(Year(Today())+1,1,1))>1),
UpdateContext({StartOfNextYear: Date(Year(Today())+1,1,1)-(Weekday(Date(Year(Today())+1,1,1)))}));

UpdateContext({EndOfLastYear: Date(Year(Today())-1,12,31)});
If(And(Weekday(Date(Year(Today())-1,12,31))>3,Weekday(Date(Year(Today())-1,12,31))<7),
UpdateContext({EndOfLastYear: Date(Year(Today())-1,12,31)+(7-Weekday(Date(Year(Today())-1,12,31)))}));
If(Weekday(Date(Year(Today())-1,12,31))<4,
UpdateContext({EndOfLastYear: Date(Year(Today())-1,12,31)-(Weekday(Date(Year(Today())-1,12,31)))}));

UpdateContext({StartOfLastYear: Date(Year(Today())-1,1,1)});
If(And(Weekday(Date(Year(Today())-1,1,1))>3),
UpdateContext({StartOfLastYear: Date(Year(Today())-1,1,1)+(7-Weekday(Date(Year(Today())-1,1,1)))+1}));
If(And(Weekday(Date(Year(Today())-1,1,1))<4,Weekday(Date(Year(Today())-1,1,1))>1),
UpdateContext({StartOfLastYear: Date(Year(Today())-1,1,1)-(Weekday(Date(Year(Today())-1,1,1)))}));

If(And(Today() >= StartOfThisYear,Today() <= EndOfThisYear)
,UpdateContext({ActiveDate: {Start: StartOfThisYear, End: EndOfThisYear, LastWeek: EndOfThisYear-6}}));
If(Today() < StartOfThisYear
,UpdateContext({ActiveDate: {Start: StartOfLastYear, End: EndOfLastYear, LastWeek: EndOfLastYear-6}}));
If(Today() > EndOfThisYear
,UpdateContext({ActiveDate: {Start: StartOfNextYear, End: EndOfNextYear, LastWeek: EndOfNextYear-6}}));

UpdateContext({StartOfThisWeek: Today() - (Weekday(Today())-1)});

UpdateContext({calcWeekNum: (DateDiff(ActiveDate.Start,StartOfThisWeek)/7)+1})```

Hope it helps someone 