Solved: DateDiff exclude weekend

MIGHSKI · ‎02-10-2022

Hi,

I'm building leave request for my team, on the submit page I want to show number of leave days selected using DateDiff but the problem is I need to exclude weekend.

As this scenario 11 is Friday and 14 is Monday so Total date shall be 2 (weekend excluded)

Any solution for this, Thank you for your help 🙂

timl · ‎02-10-2022

Hi @MIGHSKI

This formula should work for you, and calculate the difference *inclusive* of the start and end date. You would obviously replace the startDate and endDate values with references to your date picker controls. :

With({ startDate:DateValue("2022-02-11"),
       endDate:DateValue("2022-02-14")
      },
      RoundDown(DateDiff(startDate, endDate, Days) / 7, 0) * 5 +
      Mod(5 + Weekday(endDate) - Weekday(startDate), 5)
      + 1
)

The caveat with this formula is that the start and end dates must not be weekend dates.

View solution in original post

Nogueira1306 · ‎02-10-2022

Hey!!

This might help you

https://powerapps.microsoft.com/es-es/blog/excluding-weekends-and-holidays-in-date-differences-in-po...

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,
Gonçalo Nogueira

Check my LinkedIn!

Check my User Group (pt-PT)!

Last Post on Community

My website!

timl · ‎02-10-2022

Hi @MIGHSKI

This formula should work for you, and calculate the difference *inclusive* of the start and end date. You would obviously replace the startDate and endDate values with references to your date picker controls. :

With({ startDate:DateValue("2022-02-11"),
       endDate:DateValue("2022-02-14")
      },
      RoundDown(DateDiff(startDate, endDate, Days) / 7, 0) * 5 +
      Mod(5 + Weekday(endDate) - Weekday(startDate), 5)
      + 1
)

The caveat with this formula is that the start and end dates must not be weekend dates.