Incorrect DateDiff Calculation in Leave Application

(0) Share

Report

Posted on by safiranrd

I am currently creating a leave application with two scenarios

1. Working days for employee from Mon-Fri

2. Working days for employee from Mon-Sat

I have created 2 separated list for employee No 1 and 2, and the list have been working perfectly for the No 2. For No. 1 its still calculating saturday as working day so its still calculating the leave days as attached below where it should only be 1 day leave taken

But If I enter the date lets say from Fri-Sun, or Fri-Mon, the datediff working fine, only calculating Mon-Fri as the working day

I am using this formula to calculate the datediff:

If(LookUp(list1,Email_list1=User().Email,true),
UpdateContext(
 {
 daysdiff: RoundDown(
 DateDiff(
 StartDate.SelectedDate,
 EndDate.SelectedDate,
 TimeUnit.Days
 ) / 7,
 0
 ) * 5 + Mod(
 5 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate),
 5
 ) - CountIf(
 '2024 ID Public Holiday',
 HolidayStart >= StartDate.SelectedDate,
 HolidayEnd <= EndDate.SelectedDate
 )
 }
),
UpdateContext(
 {
 daysdiff: RoundDown(
 DateDiff(
 StartDate.SelectedDate,
 EndDate.SelectedDate,
 TimeUnit.Days
 ) / 7,
 0
 ) * 6 + Mod(
 6 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate),
 6
 ) - CountIf(
 '2024 ID Public Holiday',
 HolidayStart >= StartDate.SelectedDate,
 HolidayEnd <= EndDate.SelectedDate
 )
 }
)
)

Any kind of help is really appreciated, thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

v-bofeng-msft on at

Like (0)

Report
Copy link

Link copied!

Hi @safiranrd ,

Please try:

If( !IsBlank(LookUp(list1,Email_list1=User().Email)),
UpdateContext(
{
daysdiff: RoundDown(
DateDiff(
StartDate.SelectedDate,
EndDate.SelectedDate,
TimeUnit.Days
) / 7,
0
) * 5 + Mod(
5 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate),
5
) - CountIf(
'2024 ID Public Holiday',
HolidayStart >= StartDate.SelectedDate,
HolidayEnd <= EndDate.SelectedDate
)
}
),
UpdateContext(
{
daysdiff: RoundDown(
DateDiff(
StartDate.SelectedDate,
EndDate.SelectedDate,
TimeUnit.Days
) / 7,
0
) * 6 + Mod(
6 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate),
6
) - CountIf(
'2024 ID Public Holiday',
HolidayStart >= StartDate.SelectedDate,
HolidayEnd <= EndDate.SelectedDate
)
}
)
)

Best Regards,

Bof

Was this reply helpful? Yes No
safiranrd 50 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-bofeng-msft ,

Thank you for the suggestion, but everytime I use !IsBlank, my formula getting error even the daysdiff cant read the formula

Was this reply helpful? Yes No
mmbr1606 14,605 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
can you try this:

If( LookUp(list1, Email_list1=User().Email, true), UpdateContext({ daysdiff: Int( DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7 ) * 5 + If( Weekday(EndDate.SelectedDate) < Weekday(StartDate.SelectedDate), Max(0, Weekday(EndDate.SelectedDate) - 2) + Max(0, 5 - Weekday(StartDate.SelectedDate)), Max(0, Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate) - If(Weekday(EndDate.SelectedDate) = 7, 2, 1)) ) - CountIf( '2024 ID Public Holiday', HolidayStart >= StartDate.SelectedDate, HolidayEnd <= EndDate.SelectedDate, Weekday(HolidayStart) < 6 ) }), UpdateContext({ daysdiff: Int( DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7 ) * 6 + If( Weekday(EndDate.SelectedDate) < Weekday(StartDate.SelectedDate), Max(0, Weekday(EndDate.SelectedDate) - 2) + Max(0, 6 - Weekday(StartDate.SelectedDate)), Max(0, Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate) - If(Weekday(EndDate.SelectedDate) = 7, 1, 0)) ) - CountIf( '2024 ID Public Holiday', HolidayStart >= StartDate.SelectedDate, HolidayEnd <= EndDate.SelectedDate, Weekday(HolidayStart) < 7 ) }) )

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.

Greetings

Was this reply helpful? Yes No
safiranrd 50 on at

Like (0)

Report
Copy link

Link copied!
Hi @mmbr1606

Thanks for the advice, I have tried your formula but unfortunately now the datediff is not even working.. Im really wondering where it get wrong..

Im using your formula with a little adjustment bcs the given formula got an error alert
If( LookUp(list1,Email_List1=User().Email, true), UpdateContext({ daysdiff: Int( DateDiff(StartDate.SelectedDate, EndDate.SelectedDate,TimeUnit.Days) / 7 ) * 5 + If( Weekday(EndDate.SelectedDate) < Weekday(StartDate.SelectedDate), Max(0, Weekday(EndDate.SelectedDate) - 2) + Max(0, 5 - Weekday(StartDate.SelectedDate)), Max(0, Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate) - If(Weekday(EndDate.SelectedDate) = 7, 2, 1)) ) - CountIf( '2024 ID Public Holiday', HolidayStart >= StartDate.SelectedDate, HolidayEnd <= EndDate.SelectedDate, Weekday(HolidayStart) < 6 ) }), UpdateContext({ daysdiff: Int( DateDiff(StartDate.SelectedDate, EndDate.SelectedDate,TimeUnit.Days) / 7 ) * 6 + If( Weekday(EndDate.SelectedDate) < Weekday(StartDate.SelectedDate), Max(0, Weekday(EndDate.SelectedDate) - 2) + Max(0, 6 - Weekday(StartDate.SelectedDate)), Max(0, Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate) - If(Weekday(EndDate.SelectedDate) = 7, 1, 0)) ) - CountIf( '2024 ID Public Holiday', HolidayStart >= StartDate.SelectedDate, HolidayEnd <= EndDate.SelectedDate, Weekday(HolidayStart) < 7 ) }) )

Also a note - the datediff working perfectly if I enter the date from fri-sun/mon or next week it will only count the working days, but it will counted as 2 days when I submit the date from fri-sat, where sat supposed to be counted as weekend. Do you have any idea where I went wrong?

Was this reply helpful? Yes No
mmbr1606 14,605 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
can u try this modification:

If( LookUp(list1, Email_List1=User().Email, true), UpdateContext({ daysdiff: Int( DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, TimeUnit.Days) / 7 ) * 5 + If( Weekday(EndDate.SelectedDate) < Weekday(StartDate.SelectedDate), // Adjust how we handle week wrap-around Max(0, Weekday(EndDate.SelectedDate) - 2) + Max(0, 5 - Weekday(StartDate.SelectedDate)), // Proper handling of the week when it doesn't wrap Max(0, Min(Weekday(EndDate.SelectedDate), 6) - Weekday(StartDate.SelectedDate)) ) - CountIf( '2024 ID Public Holiday', HolidayStart >= StartDate.SelectedDate, HolidayEnd <= EndDate.SelectedDate, Weekday(HolidayStart) < 6 ) }), // Employee No. 2 logic (Monday to Saturday) UpdateContext({ daysdiff: Int( DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, TimeUnit.Days) / 7 ) * 6 + If( Weekday(EndDate.SelectedDate) < Weekday(StartDate.SelectedDate), Max(0, Weekday(EndDate.SelectedDate) - 2) + Max(0, 6 - Weekday(StartDate.SelectedDate)), Max(0, Min(Weekday(EndDate.SelectedDate), 7) - Weekday(StartDate.SelectedDate)) ) - CountIf( '2024 ID Public Holiday', HolidayStart >= StartDate.SelectedDate, HolidayEnd <= EndDate.SelectedDate, Weekday(HolidayStart) < 7 ) }) )

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.

Greetings

Was this reply helpful? Yes No
safiranrd 50 on at

Like (0)

Report
Copy link

Link copied!

Hi @mmbr1606

I have tried the formula and it seems work, but we still have some error:
1. Case 1 if I try to apply leave from fri-sat, it capture 1 day correctly now

2. If I try to apply from 23-26 Apr, it also captured correctly
3. When I try to apply from 23-29, it only captured as 3 days where it supposed to be 5 days
4. When I try to apply from 23-30, it captured 6 days correctly

Was this reply helpful? Yes No