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
Solved! Go to Solution.
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
formula not valid according to powerapps
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
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)
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.
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
)
So it looks like this forum is instering semi-colons in place of commas
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 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 🙂
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)
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
Check out new user group experience and if you are a leader please create your group
Did you miss the call?? Check out the Power Apps Community Call here!
See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.
User | Count |
---|---|
277 | |
236 | |
83 | |
38 | |
35 |
User | Count |
---|---|
353 | |
242 | |
129 | |
73 | |
53 |