Workday() not able to exclude a holiday list

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.

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.

3 Likes

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

3 Likes

Thank you @Heru

1 Like

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

3 Likes

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:

5 Likes

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(..., " , ")).

4 Likes

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.

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

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

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]

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

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?

1 Like

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.