Skip to main content

Notifications

Power Apps - Building Power Apps
Unanswered

Incorrect DateDiff Calculation in Leave Application

(0) ShareShare
ReportReport
Posted on by 48

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

safiranrd_0-1713769488826.png

 

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

safiranrd_0-1713770124843.png

safiranrd_1-1713770164033.png

 

 

 

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!

  • safiranrd Profile Picture
    safiranrd 48 on at
    Re: Incorrect DateDiff Calculation in Leave Application

    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

    safiranrd_0-1713867024783.png

     

    2. If I try to apply from 23-26 Apr, it also captured correctly

    safiranrd_1-1713867067932.png

    3. When I try to apply from 23-29, it only captured as 3 days where it supposed to be 5 days

    safiranrd_2-1713867116564.png

    4. When I try to apply from 23-30, it captured 6 days correctly

    safiranrd_3-1713867157842.png

     

  • mmbr1606 Profile Picture
    mmbr1606 10,444 on at
    Re: Incorrect DateDiff Calculation in Leave Application

    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

  • safiranrd Profile Picture
    safiranrd 48 on at
    Re: Incorrect DateDiff Calculation in Leave Application

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

    safiranrd_0-1713772707020.png

     

    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?

  • mmbr1606 Profile Picture
    mmbr1606 10,444 on at
    Re: Incorrect DateDiff Calculation in Leave Application

    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

  • safiranrd Profile Picture
    safiranrd 48 on at
    Re: Incorrect DateDiff Calculation in Leave Application

    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 

    safiranrd_0-1713770504657.png

     

  • Re: Incorrect DateDiff Calculation in Leave Application

    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

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.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,475

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,767

Leaderboard