cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JMAFO
Level: Powered On

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Eelman
Level 10

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

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

 

 

View solution in original post

7 REPLIES 7
Super User
Super User

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

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
Eelman
Level 10

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

@JMAFO 

What have you tried? Can you post your formula?

JMAFO
Level: Powered On

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

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

Eelman
Level 10

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

@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:

DateDiff_NoWendsHols.gif

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?

JMAFO
Level: Powered On

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

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?

Eelman
Level 10

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

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

 

 

View solution in original post

JMAFO
Level: Powered On

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

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

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (4,530)