Date Difference between two date pickers but count end date as full day

(0) Share

Report

Posted on by JMAFO

303

Hello everyone,

I need to calculate the difference between two dates, exclude weekends and holidays. The idea is that Start date is a full day and end date is also a full day. So if the start date was 2/24/2020 and the end date was 2/28/220 the total days would be 5. I tried adding a 1 to the formula I have for date difference but it interferes with when weekends are included in the date range.

Formula I'm currently using:

Value(((RoundDown(DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7,0) * 5 + Mod(5 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate), 5) -
CountIf(HolidaysCollection, StartD >= DataCardValue4.SelectedDate, StartD <= DataCardValue5.SelectedDate))+ 1) * 8)))

The CountIf() statement is referring to a company holiday collection.

The +1 I added at the end of the formula is to count the end date as a full day.

The *8 is to convert the value into hours (work day).

Thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

PowerAddict 7,316 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

How about using DateDiff with units equal to minutes (or hours) and then dividing to figure out any partial days amd then convert the partial to a full day.

Let me know if this helps.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

Thanks!
Hardit Bhatia
The Power Addict
https://thepoweraddict.com

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@JMAFO
What have you tried? Can you post your formula?

Was this reply helpful? Yes No
JMAFO 303 on at

Like (0)

Report
Copy link

Link copied!

Certainly, I added the code in my original post above.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
@JMAFO
Your formula appears to be working for me so not sure what errors you are getting? I use Set() to set a variable to the formula output and display that in a label.

My Set():
Set(vNumDays02,Value(((RoundDown(DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7,0) * 5 + Mod(5 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate), 5) - CountIf(HolidayCol, HolDate >= StartDate.SelectedDate, HolDate <= EndDate.SelectedDate))+ 1) * 8))
You do appear to have one too many )'s at the end of your formula but I just assumed that was from other things you maybe doing?

Here it is in action:
The first example above uses dates between 05/04/2020 - 05/15/2020 with 2 holidays inbetween and returns 8 days, as expected. The second, 05/08/2020 - 05/15/2020, 2 holidays, and returns 4 days.

Maybe you just have a typo somewhere?

Was this reply helpful? Yes No
JMAFO 303 on at

Like (1)

Report
Copy link

Link copied!

Thank you for your response. This actually just helped me narrow it down to what's causing it; start date or end date being on a weekend. Is the output still correct for you when you do that?

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
@JMAFO
Ah yes, it is! I sorted a way to fix this though by using two variables.

In the OnChange of DateStart datepicker add this code:
Switch(Weekday(StartDate.SelectedDate), 7, Set(vStart, DateAdd(StartDate.SelectedDate, 2,Days)), 1, Set(vStart, DateAdd(StartDate.SelectedDate, 1,Days)), Set(vStart, StartDate.SelectedDate) )
This formula sets the StartDate to the next Monday if a Saturday or Sunday is picked as the StartDate.

Then, in the OnChange of the DateEnd datepicker add this code:
Switch(Weekday(EndDate.SelectedDate), 7, Set(vEnd, DateAdd(EndDate.SelectedDate, -1,Days)), 1, Set(vEnd, DateAdd(EndDate.SelectedDate, -2,Days)), Set(vEnd, EndDate.SelectedDate) )
This formula sets the EndDate to a Friday if a Saturday or Sunday is picked as the EndDate.

Then to finish, change all of the references to StartDate.SelectedDate to vStart and EndDate.SelectedDate to vEnd. This seems to be working at my end but maybe just run a few checks yourself.

Was this reply helpful? Yes No
JMAFO 303 on at

Like (1)

Report
Copy link

Link copied!

Works like a charm. Genius and complete solution to my problem. Thank you!

Was this reply helpful? Yes No