cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PVLove
Helper III
Helper III

DateDiff Formula Incorrect

Hello All,

I'm building a Leave Calendar and cannot seem to calculate the correct number of days of leave. I'm using a DateDiff formula and displaying the number of days in a form field. The DateDiff formula is one (1) day short. If the Leave Start Date is 7/12/22 and the Leave End Date is 7/14/22 the total number of days is 2; it should be 3. If I add 1+ at the beginning or end of the formula the number 1 appears in the form field. How can I correct this? Screenshots of the form field and formula are attached. Your assistance is greatly appreciated.

Thanks!

PLove

paPTODates1.pngpaPTODates2.pngpaPTODates3.png

2 ACCEPTED SOLUTIONS

Accepted Solutions

@PVLove ,

Your formula is fine - you just need +1 at the end, but I will throw this in to manage the Delegation Warning you have there

With(
   {
      wHol:  
      Filter(
         calHolidays,
         HolidayDate >= PTOStart.SelectedDate && 
         HolidayDate <= PTOEnd.SelectedDate
     )
   },
   RoundDown(
      DateDiff(
         PTOStart.SelectedDate,
         PTOEnd.SelectedDate,
         Days
      ) / 7,
      0
   ) * 5 + 
   Mod(
      5 + Weekday(PTOEnd.SelectedDate) - Weekday(PTOStart.SelectedDate),
      5
   ) - CountRows(wHol) + 1
)

View solution in original post

You can hide it or set it to 0.

To set to 0:

If(IsBlank(PTOStart.SelectedDate) || IsBlank(PTOEnd.SelectedDate), 0, With(
   {
      wHol:  
      Filter(
         calHolidays,
         HolidayDate >= PTOStart.SelectedDate && 
         HolidayDate <= PTOEnd.SelectedDate
     )
   },
   RoundDown(
      DateDiff(
         PTOStart.SelectedDate,
         PTOEnd.SelectedDate,
         Days
      ) / 7,
      0
   ) * 5 + 
   Mod(
      5 + Weekday(PTOEnd.SelectedDate) - Weekday(PTOStart.SelectedDate),
      5
   ) - CountRows(wHol) + 1
)

 

Please Accept as Solution if it solves your question. Or just give it a Thumbs Up if it is helpful as can help others.

LinkedIn: https://www.linkedin.com/in/charlie-phipps-%F0%9F%91%A8%E2%80%8D%F0%9F%92%BB-91338715b/
YouTube: https://www.youtube.com/channel/UChmFBGU1YKIU91sNMQ7buGg
Twitter: https://twitter.com/phipps0218

View solution in original post

6 REPLIES 6
phipps0218
Super User
Super User

Wrap it in a Value() to make it a number rather than a string then + 1.

 

Please Accept as Solution if it solves your question. Or just give it a Thumbs Up if it is helpful as can help others.

LinkedIn: https://www.linkedin.com/in/charlie-phipps-%F0%9F%91%A8%E2%80%8D%F0%9F%92%BB-91338715b/
YouTube: https://www.youtube.com/channel/UChmFBGU1YKIU91sNMQ7buGg
Twitter: https://twitter.com/phipps0218

Hi @phipps0218 ,

Thanks for the quick response.  Wrapping the formula in a Value() didn't change anything.  Unless I put Value() in the wrong location.  I put it before RoundDown and then before DateDiff.  Putting Value() before RoundDown didn't trigger an error, but the 1 still appeared in the Results field.

Patti

@PVLove ,

Your formula is fine - you just need +1 at the end, but I will throw this in to manage the Delegation Warning you have there

With(
   {
      wHol:  
      Filter(
         calHolidays,
         HolidayDate >= PTOStart.SelectedDate && 
         HolidayDate <= PTOEnd.SelectedDate
     )
   },
   RoundDown(
      DateDiff(
         PTOStart.SelectedDate,
         PTOEnd.SelectedDate,
         Days
      ) / 7,
      0
   ) * 5 + 
   Mod(
      5 + Weekday(PTOEnd.SelectedDate) - Weekday(PTOStart.SelectedDate),
      5
   ) - CountRows(wHol) + 1
)

@WarrenBelz 

Thank you so very much for revising my formula and the addition to resolve the delegation warning.  The '# of Days Requested' field is now calculating the correct number.  The addition of the +1 shows in the # of Days Requested field before any data is entered in the form.  Is there a way to change that to zero (0) or hide the number until the leave dates are added to the form?

Thanks!

Patti

paPTODates4.png

You can hide it or set it to 0.

To set to 0:

If(IsBlank(PTOStart.SelectedDate) || IsBlank(PTOEnd.SelectedDate), 0, With(
   {
      wHol:  
      Filter(
         calHolidays,
         HolidayDate >= PTOStart.SelectedDate && 
         HolidayDate <= PTOEnd.SelectedDate
     )
   },
   RoundDown(
      DateDiff(
         PTOStart.SelectedDate,
         PTOEnd.SelectedDate,
         Days
      ) / 7,
      0
   ) * 5 + 
   Mod(
      5 + Weekday(PTOEnd.SelectedDate) - Weekday(PTOStart.SelectedDate),
      5
   ) - CountRows(wHol) + 1
)

 

Please Accept as Solution if it solves your question. Or just give it a Thumbs Up if it is helpful as can help others.

LinkedIn: https://www.linkedin.com/in/charlie-phipps-%F0%9F%91%A8%E2%80%8D%F0%9F%92%BB-91338715b/
YouTube: https://www.youtube.com/channel/UChmFBGU1YKIU91sNMQ7buGg
Twitter: https://twitter.com/phipps0218

@phipps0218 

Thank you for the response and the solution.  It's working perfectly!

Patti

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Check out the New Ideas Site

We are excited to announce a new way to share your ideas for Power Apps!

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