cancel
Showing results for
Did you mean:
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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

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
)``````
Super User

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.

6 REPLIES 6
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.

Helper III

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

Super User

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
)``````
Helper III

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

Super User

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.

Helper III

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

Patti

Announcements

#### 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

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

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

#### 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)