cancel
Showing results for
Did you mean:
Helper II

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

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

#### 2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

#### Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Top Kudoed Authors
Users online (3,361)