Start Date End date Diff

[pword]

I have start date , when i advance my leave request the number of days should be negative because my start date si greater than end date and should be invalid but in my apps my number of days is 5 days see screen shot below. Please help. Thank you

Here's my code on number of days

RoundDown(
DateDiff(
StartDates.SelectedDate,
EndDates.SelectedDate,
Days
) / 7,
0
) * 6 + Mod(
6 + Weekday(EndDates.SelectedDate) - Weekday(StartDates.SelectedDate),
6

) + If(Checkbox3.Value = false , 0.5)

- CountRows(
Filter(
colHolidays,
HolidayDates >= StartDates.SelectedDate,
HolidayDates <= EndDates.SelectedDate
)
)+1 /2

[pword]

@StalinPonnusamy   Can we exclude the sunday only and include monday to saturday for all the types of leave except to maternity leave include monday to sunday and holidays. 

[StalinPonnusamy · Accepted solution]

Hi @pword 

Excluded Sunday only

If(
    LeaveType.Selected.Value = "Maternity leave",
    If(
        StartDates.SelectedDate <= EndDates.SelectedDate,
        DateDiff(
            StartDates.SelectedDate,
            EndDates.SelectedDate,
            Days
        ) + 1 - If(
            Checkbox3.Value,
            0.5
        )
    ),
    (If(
        StartDates.SelectedDate > EndDates.SelectedDate,
        -Sum(
            ForAll(
                Sequence(
                    DateDiff(
                        EndDates.SelectedDate,
                        StartDates.SelectedDate,
                        Days
                    ),
                    0,
                    1
                ),
                If(
                    Weekday(
                        DateAdd(
                            EndDates.SelectedDate,
                            Value
                        )
                    ) = 7,
                    {DayCount: 0},
                    {DayCount: 1}
                )
            ),
            DayCount
        ) - If(
            Checkbox3.Value = false,
            0.5
        ) - 1/2,
        Sum(
            ForAll(
                Sequence(
                    DateDiff(
                        StartDates.SelectedDate,
                        EndDates.SelectedDate,
                        Days
                    ),
                    0,
                    1
                ),
                If(
                    Weekday(
                        DateAdd(
                            StartDates.SelectedDate,
                            Value
                        )
                    ) = 7,
                    {DayCount: 0},
                    {DayCount: 1}
                )
            ),
            DayCount
        ) + If(
            Checkbox3.Value = false,
            0.5
        ) + 1 / 2
    ) - CountRows(
        Filter(
            colHolidays1,
            HolidayDates >= StartDates.SelectedDate,
            HolidayDates <= EndDates.SelectedDate
        )
    ))
)

[pword]

Thank you @StalinPonnusamy  🙂

[pword]

@StalinPonnusamy  can you also help me with this ,

 I have a problem with my submit button, when number of days is 3 my submit button was enable  which correct but when my number of days is 5 up, my submit button was disabled even my remaining leaves is 12. See my screenshot below and my code on button in displayMode property

With(
{
wLeave:Leavetype.Selected.Value,
wLeaveAmt:Value(TextInput2_9.Text),
wSLQualified:ContacVar.'Sick Qualified'.Value,
wPLQualified: ContacVar.'Paternity Qualified'.Value,
wMLQualified: ContacVar.'Maternity Qualified'.Value,
wSPQualified: ContacVar.'SP Qualified'.Value,
wBLQualified: ContacVar.'Bereavement Qualified'.Value,
wVAWCQualified: ContacVar.'VAWC Qualified'.Value,
wVLQualified: ContacVar.'Vacation Qualified'.Value

},

If(
!'SLDCC Leave Form'.Valid ||
(
wLeave = "Paternity leave" &&
wPLQualified = "Not Qualified" &&
wLeaveAmt > Value(TextInput1_12.Text)
) ||
(
wLeave = "Bereavement Leave" &&
wBLQualified = "Not Qualified" ||
wLeaveAmt > Value(TextInput1_24.Text)
) ||
(
wLeave = "Maternity leave" &&
wMLQualified = "Not Qualified" ||
wLeaveAmt > Value(TextInput1_21.Text)
) ||
(
wLeave = "Leave for Victim of Violence (Women&Children)" &&
wVAWCQualified = "Not Qualified" ||
wLeaveAmt > Value(TextInput1_27.Text)
) ||
(
wLeave = "Parental Leave for Solo Parent" &&
wSPQualified = "Not Qualified" ||
wLeaveAmt > Value(TextInput1_18.Text)
) ||
(
wLeave = "Sick Leave" &&
wSLQualified = "Not Qualified" ||
wLeaveAmt > Value(TextInput1_9.Text)

)||
(
wLeave = "Vacation Leave" &&
wVLQualified = "Not Qualified" ||
wLeaveAmt > Value(TextInput1_9.Text)
)||
(
EndDates.SelectedDate < StartDates.SelectedDate
),
DisplayMode.Disabled,
DisplayMode.Edit
)
)

Thank you

[StalinPonnusamy]

 @pword 

It's hard without looking at the data.

Can you share these values wVLQualified and wLeaveAmt. And which one TextInput1_9.Text

[pword]

Thanks @StalinPonnusamy   its already solved.:)

[pword]

Hi @StalinPonnusamy , I need help with my App, I have a Sharepoint list compose of Employee(Title) and Company(ChoiceColumn). The Scenario is if i choose a company(Example: MCDO)  using Radio button(Mychoices:"mcdo""KFC""SNR") , my combobox should filter all Employee's working in a Mcdo Company.  I don't know where i should i start.  

Filter('Employee Details', If(Radio2_1.Selected.Value = "Mcdo", StartsWith(Mcdo, ComboBox8.Selected.Title)))

Thank you.

[StalinPonnusamy]

Hi @pword 

Can you please create a separate post, so that it helps others by looking at the post name or search?

Tag me, so that we can copy this answer to another post.

Set the Items Property of Radio button to

Choices('Employee Details'.Company)

Set the Combobox Items property to

With(
    {_Item: 'Employee Details'},
    Filter(
        _Item,
        Company.Value = Radio1.Selected.Value
    )
)

[pword]

Hi @StalinPonnusamy  I used your code but something bother me now, the Sunday was included if I file a leave on sunday, please see my screen shot below. Oct 10, 2021 is sunday , When I file a leave the number of days should be 1. My second Screenshot my number of days should be zero if today is sunday.

[StalinPonnusamy]

Hi @pword 

The calculation was missing for the same day (1 day). Fixed now. Please try now.

If(
    LeaveType.Selected.Value = "Maternity leave",
    If(
        StartDates.SelectedDate <= EndDates.SelectedDate,
        DateDiff(
            StartDates.SelectedDate,
            EndDates.SelectedDate,
            Days
        ) + 1 - If(
            Checkbox3.Value,
            0.5
        )
    ),
    (If(
        StartDates.SelectedDate = EndDates.SelectedDate,
        Sum(
            If(
                Weekday(StartDates.SelectedDate) = 1,
                0,
                1
            )
        ) - If(
            Checkbox3.Value,
            0.5
        ),
        If(
            StartDates.SelectedDate > EndDates.SelectedDate,
            -Sum(
                ForAll(
                    Sequence(
                        DateDiff(
                            EndDates.SelectedDate,
                            StartDates.SelectedDate,
                            Days
                        ),
                        0,
                        1
                    ),
                    If(
                        Weekday(
                            DateAdd(
                                EndDates.SelectedDate,
                                Value
                            )
                        ) = 1,
                        {DayCount: 0},
                        {DayCount: 1}
                    )
                ),
                DayCount
            ) - If(
                Checkbox3.Value = false,
                0.5
            ) - 1/2,
            Sum(
                ForAll(
                    Sequence(
                        DateDiff(
                            StartDates.SelectedDate,
                            EndDates.SelectedDate,
                            Days
                        ),
                        0,
                        1
                    ),
                    If(
                        Weekday(
                            DateAdd(
                                StartDates.SelectedDate,
                                Value
                            )
                        ) = 1,
                        {DayCount: 0},
                        {DayCount: 1}
                    )
                ),
                DayCount
            ) + If(
                Checkbox3.Value = false,
                0.5
            ) + 1 / 2
        ) - CountRows(
            Filter(
                colHolidays1,
                HolidayDates >= StartDates.SelectedDate,
                HolidayDates <= EndDates.SelectedDate
            )
        )
    ))
)