How to calculate week number?

[Ryball]

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

[v-micsh-msft]

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

[jeffsimons]

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

[jeffsimons]

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

[Anonymous]

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 .....

Value(If(DateDiff(DateValue(Txt_WkEnd.Text),DateAdd(Date(Year(DateValue(Txt_WkEnd.Text)),1,1),(8-Weekday(Date(Year(DateValue(Txt_WkEnd.Text)),1,1),Monday)),Days))>=0,If((RoundUp((DateDiff(Date(Year(DateValue(Txt_WkEnd.Text))-1,1,1),DateValue(Txt_WkEnd.Text),Days)+ (8-Weekday(Date(Year(DateValue(Txt_WkEnd.Text)),1,1),Monday)))/7,0))>53,1,(RoundDown((DateDiff(Date(Year(DateValue(Txt_WkEnd.Text))-1,1,1),DateValue(Txt_WkEnd.Text),Days)+ (8-Weekday(Date(Year(DateValue(Txt_WkEnd.Text)),1,1),Monday)))/7,0))),RoundDown((DateDiff(DateValue(Txt_WkEnd.Text),Date(Year(DateValue(Txt_WkEnd.Text)),1,1),Days)- (8-Weekday(Date(Year(DateValue(Txt_WkEnd.Text)),1,1),Monday)))/7,0)*-1))

[Anonymous]

Double Post

[jeffsimons]

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

Jeff

[The_Hawk]

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...

[PMH]

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....

[Anonymous]

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