Select a list of dates to exclude from future bookings.

[Mattman3000]

Good Evening

I am trying to implement my holiday table into a formula to ensure that future visits cannot be booked on holiday days for each member of staff.

To complicate things, I am trying to get the system to book 8 weeks after the previous visit eg.

WORKDAY([Date],40)

So essentially I would want the above formula to skip certain dates based on a staff members specific holiday column.

I think I need to use "SELECT" or "EXTRACTDATES" in combination with an "IF" rule to match specific staff holidays, but the complexity is a bit beyond me.

Have been scratching my head on this for weeks and havent found any similar issues in the help forums.

Hopefully someone can help.

Cheers

Matt 

[Mattman3000]

This is perhaps the closest thing I have found:

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Need-help-with-a-date-select-expression/m-p/279...

[Suvrutt_Gurjar]

You may need to share a couple of examples of your requirement so that the community could suggest better. 

However since you mentioned a list of holidays to be excluded , you may want to take a look at AppSheet WORKDAY() function's help article, just in case you have not. 

The function has option to exclude a list of dates.

https://help.appsheet.com/en/articles/2357330-workday

[Mattman3000]

Thanks

I have looked the link again, The problem is that the example I want to use:

WORKDAY("12/1/2019", 25, LIST("12/24/2019", "12/25/2019", "1/1/2020")) : 1/8/2020

does not come with explanatory text.

The above formula looks like the sort of thing I need, but I would like to get that list to change, dependent on which user is making the booking.

i.e Bob has holiday on 1/1/22 (contained in his own column in a holiday spreadsheet), He books his next visit for 8 weeks after today and it will skip his holiday.

Something like:

WORKDAY([Visitdate], 40, IF(EXTRACTDATES([Bobs Holidays]),Bob = user)

Or in english, as I am terrible at coding:

Create a date 8 weeks after the visit date using Bobs holiday list if Bob is the user.

Hopefully this is clearer

Any assistance greatly appreciated.

[Suvrutt_Gurjar]

Where the holidays of various users are stored? Can you share the structure of that table?

Also in which table [Visitdate] is there?

[Mattman3000]

Holiday table looks like this:

All	All	Bob	JIm	Fred
Good Friday	15/04/2022	07/04/2022	19/04/2022	25/03/2022
Easter Monday	18/04/2022	19/04/2022
Early May Bank Holiday	02/05/2022
Spring Bank Holiday	02/06/2022
Platinum Jubilee bank holiday	03/06/2022
Summer Bank Holiday	29/08/2022
Boxing Day	26/12/2022
Christmas Bank Holiday	27/12/2022

Visit date (or date) is part of an automation that will add a new row to a schedule table:

[Suvrutt_Gurjar]

You may wish to redesign your holiday table as it is not as per recommended practices. If  another person joins the team, you will need to add a column to the table and if someone leaves you may need to delete that column. Also column of "All" will need to be added in holidays of all people.

You may wish to read the following articles

https://help.appsheet.com/en/articles/1100263-app-design-101

https://help.appsheet.com/en/articles/895267-data-the-essentials

Instead you may wish to evaluate having holidays and leave table something like

ID	Date	Holiday Type	Employee Name	Holiday Name
2146324	15-04-2022	Public Holiday	All	Good Friday
B95BED58	18-04-2022	Public Holiday	All	Easter Monday
5F9E4D8E	02-05-2022	Public Holiday	All	Early May Bank Holiday
6ABCB3A6	02-06-2022	Public Holiday	All	Spring Bank Holiday
73ABF958	03-06-2022	Public Holiday	All	Platinum Jubilee bank holiday
655B6B7A	29-08-2022	Public Holiday	All	Summer Bank Holiday
C37317E6	26-12-2022	Public Holiday	All	Boxing Day
D4CF236F	27-12-2022	Public Holiday	All	Christmas Bank Holiday
E06891CA	07-04-2022	Employee Holiday	Bob	Bob-Personal-E06891CA
7545302B	19-04-2022	Employee Holiday	Bob	Bob-Personal-7545302B
DF3188EB	19-04-2022	Employee Holiday	Jim	Jm-Personal-DF3188EB
45C688E5	25-03-2022	Employee Holiday	Fred	Fred-Personal-45C688E5

In general your expression for Bob's appointment could be 

WORKDAY([Visitdate], 40, SELECT(HolidaysTable[Date], OR([Employee Name]="All", [Employee Name]="Bob")))

Note: Please take above as suggestions only. Please do evaluate your app requirements and design fully at your end before proceeding. It will not be possible in the community forum to continuously suggest at app design level.

[Mattman3000]

Many thanks for you suggestions so far.

Would it be possible to load holidays into the schedule directly and then virtually block off that day for the user?:

ID	Date	Customer	Employer	Description
72D032BB	18-04-2022	Mrs Smith	Bob	.........
D67678D8	18-04-2022	Mrs Jones	Fred	......
8040FFD2	18-04-2022	Bob Holiday	Bob	......
B95BED58	18-04-2022	Public Holiday	All	Easter Monday
EDD0FC50	22-04-2022	Mrs Phillips	Fred	..........
E96642EC	22-04-2022	Mr Lewis	Bob	......
6F9E4DUE	02-05-2022	Public Holiday	All	Early May Bank Holiday
5F9E4D8E	02-06-2022	Public Holiday	All	Spring Bank Holiday
8E8A2259	03-06-2022	Fred Holiday	Fred	......
F144E7D5	02-06-2022	Mr Johnson	Fred	........
6ABCB3A6	04-06-2022	Mr Adams	Bob	......

Also how would the formula you provided include the holiday dates of Jim and Fred?

Very grateful for your help so far, thanks again.

[Suvrutt_Gurjar]

As mentioned, without knowing details of your schedule and app table relations, it will be difficult to provide precise direction.

The Bob example shows just as an indicator. Depending on your app design, you will need a generic expression that will pull data for one or more employers.

May I request you to take a look at many similar template apps under the option https://www.appsheet.com/templates

None of the apps exactly match your requirements but you will get idea by looking at attendance, scheduling type of apps.

[Mattman3000]

Thank you, unfortunately the app templates are too basic for what I am trying to achieve.

"A generic expression that will pull data for one or more employers" is exactly what I need help with. I feel sure that there is an expression that can be used to achieve my goal so will try and use what you have shown, to make it work.

Thanks again

[Suvrutt_Gurjar]

Yes, I am also sure an expression can be created. If you share the table relations and what you are trying to achieve in more details,  the community could possibly suggest.