Announcements
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
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/UChmFBGU1YKIU91sNMQ7buGgTwitter: 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?
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 )
@phipps0218
Thank you for the response and the solution. It's working perfectly!
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
Congratulations to our community stars!
Expanding mentorship, skilling, and AI innovation
These are the community rock stars!
Stay up to date on forum activity by subscribing.
WarrenBelz 356 Most Valuable Professional
11manish 225 Super User 2026 Season 2
Mohsin Ali 211
