cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
omi18
Post Partisan
Post Partisan

Calculate Week Between the Selected Date

Hello All,

 

Is there any way we can calculate the total number of weeks between the selected date? 

 

Case: I have two Date pickers where users can select the start and end date. And I'm trying to display the total number of Weeks between the selected Dates. The week starts on Monday and ends on Sunday. 

 

i.e. Start Date 01/31/2022 End Date 05/08/2022 Total Weeks 14. 

Any Suggestions?

 

Thanks,

Omi

1 ACCEPTED SOLUTION

Accepted Solutions
TheRobRush
Super User
Super User

Ok so here it is for calculating NUMBER of weeks contained in a range, it counts any portion of a week Sunday-Saturday. It also calculates properly if that date range stretches from 1 year into another.

 

If(
    Year(DatePicker1)=Year(DatePicker2),
                Sum(
                    WeekNum(DatePicker2.SelectedDate)-
                    WeekNum(DatePicker1.SelectedDate))+
                    1,
                Sum(
                    WeekNum(DateValue(Concatenate(12,"/",31,"/",Year(DatePicker1))))-
                    WeekNum(DatePicker1.SelectedDate)+
                    WeekNum(DatePicker2.SelectedDate)+
                    1
                    )
)
_____________________________________________________________________________________
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

10 REPLIES 10
TheRobRush
Super User
Super User

See code below, two date pickers are datepicker1 & datepicker2

 

check before implementing what weekday() provides for Sunday and Saturday in your environment. In mine weeks run Sunday - Saturday and I believe that's default. So formula in my example uses that. If yours is different just change the 1 & 7 in formula to your sat&sun values

 

If(
    Or(Weekday(DatePicker1.SelectedDate)=7,Weekday(DatePicker1.SelectedDate)=1),
    Sum(1,
    CountIf(
    ForAll(
            Sequence(
                        DateDiff(
                            DatePicker1.SelectedDate,
                            DatePicker2.SelectedDate,
                            Days
                                )
                    ),
                    
                    If(
                        Or(
                            Weekday(DateAdd(DatePicker1.SelectedDate,Value,Days))=1,
                            Weekday(DateAdd(DatePicker1.SelectedDate,Value,Days))=7
                          ),
                          {Value:1})),Value=1)
        ),
    CountIf(
    ForAll(
            Sequence(
                        DateDiff(
                            DatePicker1.SelectedDate,
                            DatePicker2.SelectedDate,
                            Days
                                )
                    ),
                    
                    If(
                        Or(
                            Weekday(DateAdd(DatePicker1.SelectedDate,Value,Days))=1,
                            Weekday(DateAdd(DatePicker1.SelectedDate,Value,Days))=7
                          ),
                          {Value:1})),Value=1)
)
_____________________________________________________________________________________
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 misread this as weekend dates haha, one second the weeks I actually already have, and will post here

 

 

_____________________________________________________________________________________
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

Ok so here it is for calculating NUMBER of weeks contained in a range, it counts any portion of a week Sunday-Saturday. It also calculates properly if that date range stretches from 1 year into another.

 

If(
    Year(DatePicker1)=Year(DatePicker2),
                Sum(
                    WeekNum(DatePicker2.SelectedDate)-
                    WeekNum(DatePicker1.SelectedDate))+
                    1,
                Sum(
                    WeekNum(DateValue(Concatenate(12,"/",31,"/",Year(DatePicker1))))-
                    WeekNum(DatePicker1.SelectedDate)+
                    WeekNum(DatePicker2.SelectedDate)+
                    1
                    )
)
_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!

Hi @TheRobRush ,

 

Thanks for the message. With the same formula, I'm getting 15. But I think it is supposed to be 14. 

 

Screen Shot 2022-06-09 at 3.41.47 PM.png

 

Thanks,

Omi

TheRobRush
Super User
Super User

If counting any portion of this date range it would be 15, because the final date, May 1st, falls on a sunday which is day 1 of a new week. 

_____________________________________________________________________________________
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

Work Weeks in PowerApps by default run sun-saturday (theres a way to change the week start date in powerapps if you run say mond-sun instead)

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

That's a great solution @TheRobRush!

The  Weekday/Weeknum functions accept a second argument to define the start of week. If you pass StartOfWeek.Monday, I guess that would cover the final piece of this? 

TheRobRush
Super User
Super User

Great Timl, thank you for that. I never change mine so wasn't 100% sure of the modifier syntax

_____________________________________________________________________________________
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

@omi18 that looks like 

 

 WeekNum(DatePicker2.SelectedDate,StartOfWeek.Monday)

 

_____________________________________________________________________________________
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 Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

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.

Users online (2,314)