Forum Discussion
calculate date without holidays (only working days)
- 3 years ago
jcamilo1985 I am afraid I can't comment on this. Probably because my function uses recursive call. I don't know. It's quite easy to reproduce this function using List.Generate. I'll try to do that a bit later, mate.
- 3 years ago
Thank you very much for your commitment and interest in this topic AlienSx .
After several attempts, I was finally able to find the solution, but all thanks to the base that you gave me.Here I share it in case someone else needs it at some point.
In the same way thanks to the other people who read the thread and were interested.(start as date, days as number, holidays as list) as date => let dates = List.Generate( () => [ date = start, count = 1, holiday = List.Contains(holidays, start) or List.Contains({5, 6}, Date.DayOfWeek(start, Day.Monday)) ], each [count] <= days, each [ date = Date.AddDays([date], 1), count = [count] + (if [holiday] then 0 else 1), holiday = List.Contains(holidays, Date.AddDays([date], 1)) or List.Contains({5, 6}, Date.DayOfWeek(Date.AddDays([date], 1), Day.Monday)) ], each [date] ), result = List.LastN(dates, 1){0} in result
I have a different algorithm to offer, but need you to answer two questions:
- What country are you dealing with so far as holidays are concerned?
- Are we to assume that Date of Departure + One Day = Date of Departure?
first of all thank you very much ronrsnfld for coming to the solution of the thread
1 - holidays are for Colombia
2 - no, we start counting the days from the departure date.
I annex the table of holidays that I have, the real table is older
| holydays |
| 1/01/2023 |
| 7/01/2023 |
| 8/01/2023 |
| 9/01/2023 |
| 14/01/2023 |
| 15/01/2023 |
| 21/01/2023 |
| 22/01/2023 |
| 28/01/2023 |
| 29/01/2023 |
| 4/02/2023 |
| 5/02/2023 |
| 11/02/2023 |
| 12/02/2023 |
| 18/02/2023 |
| 19/02/2023 |
| 25/02/2023 |
| 26/02/2023 |
| 4/03/2023 |
| 5/03/2023 |
| 11/03/2023 |
| 12/03/2023 |
| 18/03/2023 |
| 19/03/2023 |
| 20/03/2023 |
| 25/03/2023 |
| 26/03/2023 |
| 1/04/2023 |
| 2/04/2023 |
| 6/04/2023 |
| 7/04/2023 |
| 8/04/2023 |
| 9/04/2023 |
| 15/04/2023 |
| 16/04/2023 |
| 22/04/2023 |
| 23/04/2023 |
| 29/04/2023 |
| 30/04/2023 |
| 1/05/2023 |
| 6/05/2023 |
| 7/05/2023 |
| 13/05/2023 |
| 14/05/2023 |
| 20/05/2023 |
| 21/05/2023 |
| 22/05/2023 |
| 27/05/2023 |
| 28/05/2023 |
| 3/06/2023 |
| 4/06/2023 |
| 10/06/2023 |
| 11/06/2023 |
| 12/06/2023 |
| 17/06/2023 |
| 18/06/2023 |
| 19/06/2023 |
| 24/06/2023 |
| 25/06/2023 |
| 1/07/2023 |
| 2/07/2023 |
| 3/07/2023 |
| 8/07/2023 |
| 9/07/2023 |
| 15/07/2023 |
| 16/07/2023 |
| 20/07/2023 |
| 22/07/2023 |
| 23/07/2023 |
| 29/07/2023 |
| 30/07/2023 |
| 5/08/2023 |
| 6/08/2023 |
| 7/08/2023 |
| 12/08/2023 |
| 13/08/2023 |
| 19/08/2023 |
| 20/08/2023 |
| 21/08/2023 |
| 26/08/2023 |
| 27/08/2023 |
| 2/09/2023 |
| 3/09/2023 |
| 9/09/2023 |
| 10/09/2023 |
| 16/09/2023 |
| 17/09/2023 |
| 23/09/2023 |
| 24/09/2023 |
| 30/09/2023 |
| 1/10/2023 |
| 7/10/2023 |
| 8/10/2023 |
| 14/10/2023 |
| 15/10/2023 |
| 16/10/2023 |
| 21/10/2023 |
| 22/10/2023 |
| 28/10/2023 |
| 29/10/2023 |
| 4/11/2023 |
| 5/11/2023 |
| 6/11/2023 |
| 11/11/2023 |
| 12/11/2023 |
| 13/11/2023 |
| 18/11/2023 |
| 19/11/2023 |
| 25/11/2023 |
| 26/11/2023 |
| 2/12/2023 |
| 3/12/2023 |
| 8/12/2023 |
| 9/12/2023 |
| 10/12/2023 |
| 16/12/2023 |
| 17/12/2023 |
| 23/12/2023 |
| 24/12/2023 |
| 25/12/2023 |
| 30/12/2023 |
| 31/12/2023 |