cancel
Showing results for
Did you mean:
Post Prodigy

## Calculate working days

Hi all, i know that there are many post and blogs discussions on this topic, but unfortunately neither one of them was able to resolve my issues so I'm posting it here.

Here is the story:

I have a vacation request app, user will choose start and end date of the vacation and the app will show him how many days is this.

So far its working.

Some employees however are in administration, and they don't work Saturday and Sunday, so i need to exclude weekends from the total days. The employee will select a checkbox that says "Exclude weekends", and here everything goes wrong, because the calculation shows 2 working days, where clearly they are 5 (4th, 5th, 6th, 7th, and 8th of December)

Here is my formula:

``````If(chbNoWeekDays.Value=false,DateDiff(dateFrom.SelectedDate,DateTo.SelectedDate,TimeUnit.Days)+1, RoundDown(DateDiff(dateFrom.SelectedDate, DateTo.SelectedDate,TimeUnit.Days) / 7, 0) * 5 +
Mod(5 + Weekday(DateTo.SelectedDate,StartOfWeek.Monday) - Weekday(dateFrom.SelectedDate,StartOfWeek.Monday), 5)+1)``````

Now, if you are asking why i have this +1 day, this is because without it, the system will count everything one day less so instead of 7 days the user will see 6, instead of 5 working days he will see 4, weird, but i could find other solution on that.

This is without the +1

So this was just in case you are wondering.

Any ideas what is wrong with my calculation formula, that when i take out the weekdays, the system show wrong numbers?

Regards,

Kiril

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

I'd try the following to see if that works better -

``````If(chbNoWeekDays.Value=false,
DateDiff(dateFrom.SelectedDate,DateTo.SelectedDate,TimeUnit.Days)+1,
With({ startDate:dateFrom,
endDate:DateTo
},
RoundDown(DateDiff(startDate, endDate, TimeUnit.Days) / 7, 0) * 5 +
Mod(5 + Weekday(endDate) - Weekday(startDate), 5) + 1
)
)

``````
5 REPLIES 5
Super User

I'd try the following to see if that works better -

``````If(chbNoWeekDays.Value=false,
DateDiff(dateFrom.SelectedDate,DateTo.SelectedDate,TimeUnit.Days)+1,
With({ startDate:dateFrom,
endDate:DateTo
},
RoundDown(DateDiff(startDate, endDate, TimeUnit.Days) / 7, 0) * 5 +
Mod(5 + Weekday(endDate) - Weekday(startDate), 5) + 1
)
)

``````
Solution Sage

Here's your solution. I was working on it earlier but got busy. It creates a list of days and then counts how many are Monday to Friday 🙂

``````// Use With() to set up some input parameters
With({
StartDate: DatePicker1_2.SelectedDate,
EndDate: DatePicker1_3.SelectedDate,
IncludeLastDay: true
},
// Create a list of all weekdays
With({
// Use ForAll() to loop though each day in range
WeekDays: ForAll(
// Sequence() creates a number list
// DateDiff() returns the number of days between 2 dates
// +1 if we are to include the last day
Sequence( DateDiff( StartDate, EndDate) +If( IncludeLastDay, 1)),

// Weekday() returns the day number of the week, Monday 1, Sunday 7
},
// Use CountIf() to count how many days are weekdays
// Value is the weekday number
// 'in' returns true if the Value is in our list
// 1-5 is Monday to Friday

CountIf( WeekDays, Value in [1,2,3,4,5])
)
)``````
Post Prodigy

Thank you @timl it's working as charm.

Solution Sage

Hi @Radoslavov, I wasn't happy with my solution so I revisited this. I also tested it against @timl's solution. Unfortunately timl's solution fails.

My original code uses a loop and a lookup which isn't very efficient so I rewrote it without this.

Here's my updated solution:

``````// Use With() to set up some input parameters
With({
StartDate: DatePicker1_2.SelectedDate,
EndDate: DatePicker1_3.SelectedDate
},
With({
// Make sure our dates are the right way around
PastDate: Min( StartDate, EndDate),
FutureDate: Max( StartDate, EndDate)
},
With({
// Work out total days, add 1 to include the last day
Days: DateDiff( PastDate, FutureDate) +1,

// Work out how many working days in the first week and last week
// This corrects for partial weeks in range
WorkingDaysBefore: Max( 0, 8- Weekday( PastDate, StartOfWeek.Monday) -2),
WorkingDaysAfter: Min( 5, Weekday( FutureDate, StartOfWeek.Monday))
},
With({
// Int() removes numbers after the decimal point
// This gives us the number of whole weeks, less the days we already counted
Weeks: Int((Days -WorkingDaysBefore -WorkingDaysAfter) /7)
},
// The answer is the weekdays in whole weeks
// Add on the weekdays in the partial weeks before and after
(Weeks *5) +WorkingDaysBefore +WorkingDaysAfter
)
)
)
)``````

Testing:

Monday to Sunday:

Single day:

Long weekend Friday to Monday:

Weekend, Saturday to Sunday:

2 weeks Sunday to Sunday:

2 weeks + 1 day, Saturday to Sunday:

Resolver III

@Chris-D Logged in just to say thank you for your solution! I've been struggling for the past couple of hours and both ChatGPT and Copilot aren't being helpful. Yours are the only one that works with all the dates I tested.

Announcements

#### Celebrating the May Super User of the Month: Laurens Martens

@LaurensM  is an exceptional contributor to the Power Platform Community. Super Users like Laurens inspire others through their example, encouragement, and active participation. We are excited to celebrated Laurens as our Super User of the Month for May 2024.   Consistent Engagement:  He consistently engages with the community by answering forum questions, sharing insights, and providing solutions. Laurens dedication helps other users find answers and overcome challenges.   Community Expertise: As a Super User, Laurens plays a crucial role in maintaining a knowledge sharing environment. Always ensuring a positive experience for everyone.   Leadership: He shares valuable insights on community growth, engagement, and future trends. Their contributions help shape the Power Platform Community.   Congratulations, Laurens Martens, for your outstanding work! Keep inspiring others and making a difference in the community!   Keep up the fantastic work!

#### Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Check Out the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community.  We can't wait to see what you "cook" up!