cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yerlasdutco
Helper II
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

 
 

Capture.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
TheRobRush
Super User
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!

View solution in original post

3 REPLIES 3
TheRobRush
Super User
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!
TheRobRush
Super User
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!

 

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
            DateAdd(
                ///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!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

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

365 EduCon 768x460.png

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.

Users online (5,520)