cancel
Showing results for
Did you mean:
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
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!
10 REPLIES 10
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(
),
{Value:1})),Value=1)
),
CountIf(
ForAll(
Sequence(
DateDiff(
DatePicker1.SelectedDate,
DatePicker2.SelectedDate,
Days
)
),

If(
Or(
),
{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!
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!
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!
Post Partisan

Hi @TheRobRush ,

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

Thanks,

Omi

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!
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!
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?

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

Announcements

#### Power Platform Community Call

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

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

Top Solution Authors
Top Kudoed Authors
Users online (2,314)