Workday() not able to exclude a holiday list

khuslid
Participant V

Hi.
I’m making a progress plan and want to exclude several periods of holidays.
I’ve made a separate Holidaytable with the option of up to five periods [Date_From] and [Date_To].
In order to get a list of dates to exclude in “Workday()”, I’ve used the tip from @Aleksi with:
TOP (LIST ([Date_From],
[Date_From] +1,
[Date_From] +2,
[Date_From] +3,
[Date_From] +4,
[Date_From] +5,
[Date_From] + …,
[Date_From] +30),
HOUR ([Date_To] - [Date_From]) / 24)
I do this 5 times (adding the lists). This works great (big expression, though). I get one single list of dates from my 5 different holiday periods.

But when I am to exclude these dates from my progress plan periods, I am struggling!
I tried both a virtual column and a permanent one with:
Workday([Project_start], [No of workdays], Holidaytable[List_of_non-workdays])
The error I get is: “Holidaytable[List_of_non-workdays]” has the wrong element type: List.
But in @Steve 's manual on Workday(), it says that it has to be a list.
I’ve also tried a workaround and collected the list from a field that is not a LIST type, but then the error is the oposite (the last part of Workday() has to be a list).

I’m starting to suspect that the workday() formula has to make the list itself from a table with several records, and doesn’t accept an already complete list.
But if that is the case, how to work around this?
I cannot type a new record for each holiday date for a holiday of eg. 4 weeks.

0 29 1,590
29 REPLIES 29

Hi @khuslid,

I believe your observations are correct. The WORKDAY(0 function seems to be producing error if the list iexpression from other table is directly mentioned in the WORKDAY() expression.

It worked during my testing with the following workaround on a similar test setup as yours.

Please create a VC in your HolidayTable with the expression such as

Holidaytable[List_of_non-workdays] .

Say this VC is called [CombinedHolidayList]

Now you may have your workday expression as

Workday([Project_start], [No of workdays], [CombinedHolidayList] )

Hope this helps.

It seems there’s still an issue with the WORKDAY function.

Thank you @Heru

TDhers
Participant V

Thank you both for reporting this. @natalie, do you mind taking a look at this to see why we can’t pass a list. Thank you
Thierry

Hi, I will explain how expression Holidaytable[List_of_non-workdays] is handled by AppSheet:

Referencing a table and column together (a table-column reference ) constructs a list of all values in that column of that table. Holidaytable[List_of_non-workdays] produces a list of all List_of_non-workdays column values from the Holidaytable table. So, the type of that expression is a List of a List of Date. WORKDAY() expects a List of Date or List of Ref to Date.

So, to fix the expression, we should give WORKDAY() a single row of List_of_non-workdays, instead of all of the rows. If you want the 1st row used, you can try: WORKDAY([Project_start], [No of workdays], INDEX(Holidaytable[List_of_non-workdays], 1)). If you want the 2nd row used, replace “1” with “2”.

@khuslid try this and let me know if it works!

@Heru the problem in the other post about WORKDAY (Workday() function not working) should be fixed by now. The problem in that case was that WORKDAY wasn’t expecting a List of Ref to Date, only List of Date, but now both are accepted.

Links to more docs:

Steve
Participant V

If you need all of the dates in the list of lists, use this:

WORKDAY(
  [Project_start],
  [No of workdays],
  SPLIT(Holidaytable[List_of_non-workdays], " , ")
)

(i.e., wrap Holidaytable[List_of_non-workdays] in SPLIT(..., " , ")).

khuslid
Participant V

Hi guys!
Thanks a lot for all your effort of helping!
I’ve tried all of your suggestions now, but I still cannot make it work.
I’ll explain step by step, so hopefully you catch me in something stupid I’ve done here:)

@Suvrutt_Gurjar I had already tried your suggestion of “Please create a VC in your HolidayTable with the expression such as Holidaytable[List_of_non-workdays]”. This doesn’t work in my APP. It returns the error: “Holidaytable[List_of_non-workdays]has the wrong element type: List.”

@natalie Thanks for clarifying. You confirmed what I suspected. But your explanation doesn’t really solve the problem. We have long periods of non-workdays. FromDate-ToDate. How can these dates end up as single dates in a table? Lets say there are 6 weeks of non-workdays. Then we have to type 30 records (rows) in a table for that (5x6)! Not very efficient.

So… I put all my hopes into @Steve 's reply of making lists of list. It made sense. But unfortunately it came up with the same error: “Parameter SPLIT(Holidaytable[List_of_non-workdays],”, “) has the wrong element type: Text”

I’ve tried with Holidaytable[List_of_non-workdays] being both a VC and standard. As column type DATE and LIST.

Remember: My Holidaytable does not produce a new record of each non-workday. It has the option of regestering up to 5 periods of non-workdays. (I might want to increase that to 10 periods actually)
[Start_DATE_Holidayperiod1],
[End_DATE_Holidayperiod1],
[Start_DATE_Holidayperiod2],
[End_DATE_Holidayperiod2],
[Start_DATE_Holidayperiod3],
[End_DATE_Holidayperiod3],
[Start_DATE_Holidayperiod4],
[End_DATE_Holidayperiod4],
[Start_DATE_Holidayperiod5],
[End_DATE_Holidayperiod5].
So I have to collect these dates in these 5 periods and make them work in a Workday() expression in another table (My progress plan).
I’ve made a VC collecting all the non-workdays with the expression:
TOP (LIST ([Start_DATE_Holidayperiod1],
[Start_DATE_Holidayperiod1] +1,
[Start_DATE_Holidayperiod1] +2,
[Start_DATE_Holidayperiod1] +3,
…,
[Start_DATE_Holidayperiod1] +30),
HOUR ([End_DATE_Holidayperiod1] - [Start_DATE_Holidayperiod1]) / 24)
+
TOP (LIST ([Start_DATE_Holidayperiod2],
[Start_DATE_Holidayperiod2] +1,
…and so on…
I do this 5 times (adding the 5 lists of the 5 holiday periods).
I get the complete “list” of non-workdays in one record (Cell).
How do I split them up to work with Workday()?
I guess making 5 lists from the 5 holiday periods makes no difference.

Am I doing something wrong here?
(Again, thanks for all help and efforts here!!!)

PS: If you come up with ideas of workarounds, I gladly accept. Eg. an action of some kind producing a series of records of non-workdays between two chosen dates? In that case I guess Workday() will work.

Try this instead:

WORKDAY(
  [Project_start],
  [No of workdays],
  (
    LIST(DATE(""))
    + SPLIT(Holidaytable[List_of_non-workdays], " , ")
    - LIST("")
  )
)

I believe the problem was that the result of a SPLIT() expression was a List of Text, but WORKDAY() wants a List of Date. To give WORKDAY() what it wants:

  1. Construct a list starting with one blank Date value (LIST(DATE(""))). A list’s type is determined by the type of its first element, so with this first element being a Date, the list’s type is Date.

  2. Add the result of the SPLIT() expression (... + SPLIT(...)). As it’s elements are being added to the initial List of Date (from (1)), each added element will be given the Date type.

  3. Finally, subtract the blank date added initially (... - LIST(""); from (1)). At this point, the list’s type is established as Date, so removing the first element won’t affect that.

    Note that list subtraction has the side-effect of removing duplicates from the result, but that shouldn’t be a problem here because we only care that the dates occur in the list, not how many times each occurs.

See also:





This is really spooky!!
I tried your suggestion @Steve , and got “green light” entering the expression.
But now the UX inline, form or detail won’t show!!!
So I cannot test it.

In the beginning I thought it had to be something with the connection to the DB (SQL Server) or other things. I regenerated and checked all connections.

So in the end I removed the new Workday() expressing I got from @Steve , and all views immediately appears again in UX. Crazy! No error message, no warning.
The expression:
WORKDAY(
[Project_start],
[No of workdays],
(
LIST(DATE(""))
+ SPLIT(Holidaytable[List_of_non-workdays], " , “)
- LIST(”")
)
)
actually removes all views of my Progress plan in UX!
This must clearly be a bug in Appsheet?
Any ideas?

Yes, this seems like a bug. If you are able, please open a support ticket with support@appsheet.com, so that we can continue figuring out how to fix this. If I help you through the support ticket, I will be able to look at your app and see what has happened.

Edit: Nevermind, I see there is a support ticket open already. Let’s continue our conversation there.

Whoa!

Ah, so you already have a VC with the complete list of the non-workdays in one cell. Is this VC the same VC as `List_of_non-workdays"? Which row in that VC is the cell in? The type of the VC is List of Date, right?

Yes.
The long expression of
TOP (LIST ([Start_DATE_Holidayperiod1],
[Start_DATE_Holidayperiod1] +1,
[Start_DATE_Holidayperiod1] +2,
[Start_DATE_Holidayperiod1] +3,
…,
is in the VC in the Holiday table.
Its a list of dates, yes.
Didn’t quite understand your question: Which row in that VC is the cell in?

If the cell with all the non-workdays is in the first row of the List_of_non-workdays VC, then I would re-suggest my initial suggestion of using:

WORKDAY([Project_start], [No of workdays], INDEX(Holidaytable[List_of_non-workdays], 1))

The number “1” refers to which row of the VC is the cell with all the non-workdays in it.

However, your earlier reply stated that my suggestion did not work. So, I was wondering if the cell with all the non-workdays in it is not actually in the first row of the VC.

Sorry. I dont understand. Maybe I am not fully into the therms here.
What do you mean by “CELL”?
I have a VC (at the bottom of the permanent colums of course).
Sorry, I feel stupid now…

No need to feel stupid! I think we are very close to getting the solution.

I read your initial reply to my formula suggestion and I want to know more about why it didn’t work. Did using that suggestion return the wrong date ranges or cause an app error?

Ok, thanks.
But all the dates of non-workdays from my 5 perids end up in a “list” in my one and only VC (All dates are separated by ,(comma) here. MAked yellow below.
When you start writing/asking about which CELL and which COLUMN and which ROW to use, you loose me.
3X_3_4_34257c221f25e0513a8a07cfec41f2742b41d0b8.png

Yes, columns/rows/cells are deeper concepts and I should explain myself better. Thanks for your patience.

If you want to hide the yellow field from the form view, you can unset the toggle under “Show?” for Fridager1 (example shown below). If that toggle is grey, it will not show the VC in the form.

Thanks, this I know.
My problem is making the list of non-workdays work in a Workday() expression:)
(Just wanted to show you the VC result, to help you understand)

“Fridager1” is what we have referred to as Holidaytable[List_of_non-workdays]

This is likely my fault, I still don’t understand why using INDEX(Holidaytable[Fridager1], 1) doesn’t work when you enter it in your WORKDAY() expression. I have a couple guesses:

  1. The WORKDAY() expression shows a red error when you try my suggestion and click Save
  2. The WORKDAY() expression does not show a red error after you try my suggestion and click Save, but the expression computes the wrong output

Is the problem one of these?

Hi @natalie
Thanks for not giving up on me. (I had to sleep for some hours - different timezone)
Your expression works in the way that it gives no error message.
It even picks some of the dates from Holidaytable[Fridager1] and excludes them from workdays, but only a few.
I’m trying to figure out the pattern of which dates of periods it excludes (like it should), and which it doesn’t pick. But so far I don’t understand it…
In my test I had put in 5 different periods of holidays. Not all in the same “row”.
2 periods i one, 2 in another and 1 in the last. (total of 5).
(The reason for building the table this way, is that I also have a column named [Project]. So I can have different periods of holidays for each project in the different project plans. But I haven’t started with filtering on the project yet. I want to make this workday() work first.)
In my test I have following 5 holiday-periods:
13.-16. of June, 17.-19. of June, 17.-18. of June, 26.-27. of June and 8.-24. of June.
I am aware of many doublet dates her, but it shouldn’t matter.
So when I start an activity 11. of June and write 10 workdays, it comes up with 1. of July.
If it were to pick no holidays (just weekends), it would end up with the result of 24. of June.
And if it were to work like it should and pick all of the non-workdays it should result in 13. of July.
But the result is something in between… 1. of July.

The third argument to WORKDAY() ex expected to be a List, but INDEX() returns a singular value.

3X_9_4_944b56a1869414bfd1fad8fbd8422120e14057af.png

TOP() returns a List of the first N elements from its List input, so it can be used successfully to get the first element of the list:

3X_4_8_4879cfab9854dec9c404f266ca2bad4e0acc97b6.png

Going back to INDEX(): should we need something other than the first element of the list, wrap the INDEX() output with LIST() to make a one-item list:

3X_1_a_1a877c732f82ae3924e7decac007957e80d1c9ae.png

See also:



khuslid
Participant V

Does this help to understand (from my Holiday table):

We resolved this issue in the support channel, but I’ll post the solution here so others may find the same fix.

There will sometimes be an error where the expression

SPLIT(Holidaytable[List_of_non-workdays], " , ")

returns a list of elements formatted like “Wed Jun 10 2020…”, instead of “6/10/20”, which is a bug that we’ll need to fix. Meanwhile, the temporary workaround is to use this instead:

EXTRACTDATES(TEXT(Holidaytable[List_of_non-workdays]))

So, Kristofer’s working solution was:

WORKDAY(
[Project_start],
[No of workdays],
EXTRACTDATES(TEXT(Holidaytable[List_of_non-workdays]))
)

Well done!

Bahbus
Participant V

I was actually coming here this morning to check to see if anyone had tried the EXTRACT versions. And, well, there you are.

khuslid
Participant V

I’m sorry, but Natalie’s solution doesn’t work.
I’ve been busy with other things and did not have time to check it fully before now.
I tested in both VC and standard.
I assumed it worked since AS got access and looked into the APP.
The expression gives “Green light”, but doesn’t withdraw any holiday dates. Just weekends as usual.

yani
Participant I

I’ve tried all the suggested workaround above and all didnt work. I’ve notice that WORKDAY(), if added list of Holidays only works if the list of dates is in this format “mm/dd/yyyy”.
Here in SG we use “dd/mm/yyy” so just like others, I struggled in getting the next working day taking Holidays in consideration.

Here’s my workaround and it works for me. Posting it here as it may help others who are having the same issue in WORKDAY() that includes holiday.

In my Holiday table (SGPH), I’ve added a virtual column and name it [StartDateAsString] and make the type as Text.
In App formula, I’ve converted the Holiday Date into Text with “mm/dd/yyyy” format
The formula goes like this: TEXT([Start], “mm/dd/yyyy”)

In my Main table, I have added two virtual columns, 1st column is to get the list of Holidays from my SGPH table using the virtual column [StartDateAsString] I’ve added.
2nd column is where I apply the WORKDAY() formula.

In the 1st column, the formula goes like this: SPLIT(SELECT(SGPH[StartDateAsString], TRUE), “,”)
Here I set the Type as List and Element type is Date.

In the 2nd column, the formula is like this: WORKDAY([Field1], 1, [PH])
I set the Type as Date

Doing the above steps, Im able to get the next working day and taking the holidays in consideration.

3X_b_4_b4b388688650ead5b3498ff21152c61e85f9a689.png

Note that in my form display, it still shows the “dd/mm/yyyy” since I am using English (United Kingdom) locale in my Chrome and App Data Locale.

Hope this helps.

Top Labels in this Space