cancel
Showing results for
Did you mean:
Helper III

## Calculate number of week

Hi Folks,

I have a dropDown menu, which shows workdays 7 weeks period from Today().

How can I calculate Number of week?

For example: I Choose from dropdown: 14.06 - 18.06

Text on label: Week 25, 18 June

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

the following will return to you a week number, which you can then concatenate with date you selected.

THOUGH be advised, not sure how you are calculating your weeks, but the week of the 14th-18th was week 24 of the year, this week is week 25. First week does not start until the week begins in January, if Jan1 is halfway through a week that counts on as final week of previous year in formulas. Don't know that there's a way around that part. Other than adding in a +1, but that won't work for weeks at start and end.

``````RoundDown((RoundUp(dateholder.SelectedDate-Date(Year(dateholder.SelectedDate-Weekday(dateholder.SelectedDate-1)+4),1,3),0)+
Weekday(Date(Year(dateholder.SelectedDate-Weekday(dateholder.SelectedDate-1)+4),1,3))+5)
/7,0)``````

in this example dateholder is my calendar

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!
3 REPLIES 3
Super User

the following will return to you a week number, which you can then concatenate with date you selected.

THOUGH be advised, not sure how you are calculating your weeks, but the week of the 14th-18th was week 24 of the year, this week is week 25. First week does not start until the week begins in January, if Jan1 is halfway through a week that counts on as final week of previous year in formulas. Don't know that there's a way around that part. Other than adding in a +1, but that won't work for weeks at start and end.

``````RoundDown((RoundUp(dateholder.SelectedDate-Date(Year(dateholder.SelectedDate-Weekday(dateholder.SelectedDate-1)+4),1,3),0)+
Weekday(Date(Year(dateholder.SelectedDate-Weekday(dateholder.SelectedDate-1)+4),1,3))+5)
/7,0)``````

in this example dateholder is my calendar

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!
Super User

I have been thinking about it and came to the conclusion that it's silly that all the formulas I have seen return the week number of the year pretending like January 1st's week does not count if it falls late in the week. I am sure plenty of companies would count the week with January 1 in it as the first week of their year. So below you will find a formula I have been working on today. It SHOULD always return the week number of the date selected (dateholder is name of control in my code) as if January 1st was week 1, regardless if that was on Sunday, or Friday.

``JUST FOUND A BUG, WORKING IT OUT AND WILL REPOST``

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!
Super User

Tried to label everything, post making the formula. If anyone feels like relabeling it in a way they think is easier for newcomers to understand in the future, be my guest.

Hope this is useful to everyone!

``````If(
////Check if the user Selected January 1st
And(Month(dateholder.SelectedDate)=1,Day(dateholder.SelectedDate)=1),
/// If so it is week 1
1,
///If Not, Check if it is same week as January 1
If(
dateholder.SelectedDate
<=
////determine last day in the week of January 1
///Get January 1 this Year
DateValue("1/1/"
&
Year(dateholder.SelectedDate)),
///add value of 7 minus day number of January 1, resulting in last day of week Jan 1
7
-
Weekday(
DateValue(
"1/1/"
&
Year(dateholder.SelectedDate))),
Days),
//////If so it is Week 1
1,
//////If Not, Check If it is a Sunday, or if January 1 is on Sunday
If(
Or(
Or(
DateDiff(
DateValue("01/"
&
Sum(2,7-Weekday(DateValue("01/01/"&Year(dateholder.SelectedDate))))
&
"/"
&
Year(dateholder.SelectedDate)),
dateholder.SelectedDate,
/////If it's 0 it's first Sunday after january 1
Days)=0,
////multiple of 7 identifier (Sunday id)
Mod(
DateDiff(
DateValue("01/"
&
Sum(2,7-Weekday(DateValue("01/01/"&Year(dateholder.SelectedDate))))
&
"/"
&
Year(dateholder.SelectedDate)),
dateholder.SelectedDate,
/////if date value divided by 7 has a remainder of 0, its a Sunday
Days),7)=0),
////Is January 1 & my selected date both on Sunday?
And(
Weekday(DateValue("01/01/"&Year(dateholder.SelectedDate)))=1,
Weekday(dateholder.SelectedDate)=1)),
////Sunday Formula
RoundUp(
DateDiff(
DateValue("01/"
&
Sum(2,7-Weekday(DateValue("01/01/"&Year(dateholder.SelectedDate))))
&
"/"
&
Year(dateholder.SelectedDate)),
dateholder.SelectedDate,
Days)
/7,0)
+2,
////All Other Days formula
RoundUp(
DateDiff(
DateValue("01/"
&
Sum(2,7-Weekday(DateValue("01/01/"&Year(dateholder.SelectedDate))))
&
"/"
&
Year(dateholder.SelectedDate)),
dateholder.SelectedDate,
Days)
/7,0)
+1)))``````

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!

Announcements

#### Celebrating the May Super User of the Month: Laurens Martens

@LaurensM  is an exceptional contributor to the Power Platform Community. Super Users like Laurens inspire others through their example, encouragement, and active participation. We are excited to celebrated Laurens as our Super User of the Month for May 2024.   Consistent Engagement:  He consistently engages with the community by answering forum questions, sharing insights, and providing solutions. Laurens dedication helps other users find answers and overcome challenges.   Community Expertise: As a Super User, Laurens plays a crucial role in maintaining a knowledge sharing environment. Always ensuring a positive experience for everyone.   Leadership: He shares valuable insights on community growth, engagement, and future trends. Their contributions help shape the Power Platform Community.   Congratulations, Laurens Martens, for your outstanding work! Keep inspiring others and making a difference in the community!   Keep up the fantastic work!

#### Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Check Out the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community.  We can't wait to see what you "cook" up!