Calculate number of week

[Yerlasdutco]

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

[TheRobRush · Accepted solution]

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

[TheRobRush]

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

[TheRobRush]

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