Hi
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!
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
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
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?
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
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
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