Unanswered

Counting days between two dates excluding weekends

(0) Share

Report

Posted on by danuttah

Hi there,

I'm having some issues with I'm hoping someone can help with. I've looked around for solutions but can't quite work out how to integrate them into what I have.

Basically, I have a helpdesk application and need to work out a percentage of tickets closed within two days over a given period.

I have created date and closed date fields within the Sharepoint list, and I've got the following code to create a collection, from this I've counting the number of rows in this collection and then comparing this with total number of tickets for that period. However, the issue I have it that this count includes weekends, so if a ticket was raised on Friday, and closed on Monday, it would not be counted - when it fact it should be. Any ideas on how to solve this would be very much appreciated.

ClearCollect(

ClosedWithinTwoDays_May,

Filter(

<sharepointlistname>,

Created>= DateValue("05/01/2024") && DateDiff(Created, closed_date) <= 2

)

);

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Sort by

v-mengmli-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @danuttah ,

Please try this.

ClearCollect( ClosedWithinTwoDays_May, Filter( <sharepointlistname>, Created>= DateValue("05/01/2024") && Mod(5 + Weekday(closed_date)-Weekday(Created),5)<= 2 ) );

Best regards,

Rimmon

Was this reply helpful? Yes No
CU05080617-0 136 on at

Like
a
(1)

Report
Copy link

Link copied!

Hello @danuttah .

Please try this:

I have two date pickers, from 01/06/2024 - 30/06/2024 and a textbox with the code:

With(
{
TotalDays: DateDiff(DatePickerStart.SelectedDate, DatePickerEnd.SelectedDate) + 1,
CompleteWeeks: RoundDown((DateDiff(DatePickerStart.SelectedDate, DatePickerEnd.SelectedDate) + 1) / 7, 0),
WeekdayStart: Weekday(DatePickerStart.SelectedDate),
WeekdayEnd: Weekday(DatePickerEnd.SelectedDate)
},
TotalDays - (CompleteWeeks * 2 + If(WeekdayEnd >= WeekdayStart, Max(0, WeekdayEnd - 6) + Min(0, WeekdayStart - 1), 2))
)

It seems to work the way you said.
Best regards.
Nick.

Was this reply helpful? Yes No
CarlosFigueira Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
@NickFot answer should give you the number of days excluding weekdays for the general case. Your specific case is a little simpler, as you only need to find out whether the start day is within two days (excluding weekends) of the end day, so you can use something like the expression below, which takes the difference between the two days, and if they are in different weeks, subtract 2:

Filter( <sharepointlistname>, Created >= DateValue("05/01/2024") && (DateDiff(Created, closed_date, TimeUnit.Days) - If(WeekNum(Created) <> WeekNum(closed_date), 2, 0)) <= 2 )

If they are in the same week, you'll get the actual date difference; if they are in subsequent weeks, you will get the date difference excluding weekends. If they are in days more than one week apart, the result of that expression won't be the exact value, but it will be much more than 2 anyway.

Hope this helps!

Was this reply helpful? Yes No