Finding out if a date range is already taken?

(0) Share

Report

Posted on by pmwhelan

377

I have an app which logs sick leave for users.

It stores the leave in a SharePoint list.

Each row in the list has

User,StartDayOfSickness, EndDayOfSickness

(End can be equal to Start - for one day - but not earlier than Start)

In the App I allow HR to add sickness for a user by having a Gallery to pick the user and then two DatePickers (dpStart, dpEnd)

Is there a way to disable the Submit button if HR are trying to add days that already are in the list?

I realise this could be very complex.

If leave exists for

Me,17/05/2023,17/05/2023

Me,18/05/2023,19/05/2023

Me,22/05/2023,25/05/2023

and then in the form I try to add

dpStart 19/05/2023

dpEnd 19/05/2023

then it should say "Leave exists already"

Or if I try to add

dpStart 25/05/2023

dpEnd 30/05/2023

then the same message that "Leave exists already"

You would have to loop through the Date Range and the items in the list and it's a bit beyond me!

Thanks

Categories:

Building Power Apps

All responses (6)

Answers (1)

Sort by

Akser 1,546 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @pmwhelan,

If the result of the formula is not 0, then you should your message

CountRows( Filter( MySharePointList, User = CurrentUser, IsBlank(dpStart) Or (StartDayOfSickness >= dpStart And EndDayOfSickness =< dpStart), IsBlank(dpEnd) Or (StartDayOfSickness >= dpEnd And EndDayOfSickness <= dpEnd) ) )
pmwhelan 377 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Akser
Thanks but that doesn't seem to work.
It's showing 0 for the results but I have two rows for a user

StartDayOfSickness EndDayOfSickness
And the App Date Pickers look like this ....

Thanks
P
pmwhelan 377 on at

Like
a
(0)

Report
Copy link

Link copied!

I see a similar question but I've tried that and get 0 also (even when there is an overlap)

https://powerusers.microsoft.com/t5/Building-Power-Apps/Identify-overlapping-of-date-range/td-p/1539644

Filter(
ProjectArrostosCalls,
LPA.Value = galLPAs.Selected.Email,
(dpStart<EndDayOfSickness && dpStart>=DayOfSickness),
(dpEnd<=EndDayOfSickness && dpEnd>DayOfSickness),
(dpEnd>=EndDayOfSickness && dpStart<=DayOfSickness)
)
pmwhelan 377 on at

Like
a
(0)

Report
Copy link

Link copied!

Another solution from (https://powerusers.microsoft.com/t5/Building-Power-Apps/Evaluate-to-see-if-two-date-fields-are-in-range-of-any-existing/td-p/391730)
which isn't working for me either...

Filter(ProjectArrostosCalls,
LPA.Value = galLPAs.Selected.Email,
Or(
And(
DayOfSickness>=dpStart,
DayOfSickness<=dpEnd
),
And(
EndDayOfSickness>=dpStart,
EndDayOfSickness<=dpEnd
)
)
)
Verified answer

WarrenBelz 156,538 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @pmwhelan ,

A bit like booking a room - there are three possibilities

The Start data is in the period

The End date is in the period

The period spans the entire selected dates

Filter( ProjectArrostosCalls, LPA.Value = galLPAs.Selected.Email && ( ( DayOfSickness >= dpStart && DayOfSickness <= dpEnd ) || ( EndDayOfSickness >= dpStart && EndDayOfSickness <= dpEnd ) || ( DayOfSickness <= dpStart && EndDayOfSickness >= dpEnd ) ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps
whykay1029 on at

Like
a
(0)

Report
Copy link

Link copied!

This work perfectly. what if i want to edit an existing request to extend and cancel existing one to edit.
for instance, I have a request, check in date: 23 of May to 25th of May. Another request for 27th to 29th of May. I need to cancel the second booking to extend the date for the first booking.
how do go about the validation to be sure the second booking was cancelled to extend the date.