How to calculate Date,houres for task completion in working days + public holydays

I need help: I’m working on a board where I have to do date calculations.
The challenge is that I have to record a date when a task is created, one from which the task is actually initiated and expects to set an automatic deadline of 20 working days for the completion of the task and to place the expected date of completion after 20 working days in a column. In addition, if the request is created after 06 pm, the date of the task has to be pushed to the next business day starting at 09 hours.

That is to say:
Column A: Date of receipt of task
Column B: is = [Column A] if the task entered until 06pm, otherwise [Column B] will have a later working date than [Column A] and will start at 09h00
Column C: is the number of days to complete the task = 20 working days (subtracting, in addition to weekends, any other date considered to be a public holiday
Column D: Date of completion = [Column B]+[Column C]

Thanks in davance

1 Like

hi @oliverio

Welcome to the community. That is a very interesting use case. To even start working on a use case like this you need to define what a public holiday is. See also: Public holiday - Wikipedia

The issue is that public holidays are different country by country and year over year. It is very hard to set a definition that is 100% accurate. Working days in monday are defined as 5 days per week, every week.

1 Like

Thank you for your reply and thankfully help on this case.

This case I’m bringing here, is already been built in Microsft Excel.
The public holidays and other dates I want to consider as a holiday or leave are on a separate sheet.
I don’t mind making a similar sheet in Monday with manual input as long as I know how to reference this sheet in my formulas.

That is a good start :slight_smile:

Unfortunately formulas can’t access data form other boards. The only way I see to get this working is to use Integromat / Zapier or a custom app to uses the monday API.

:unamused:
This is really unfortunate…

I had high expectations for this web app. Well, I’ll see if I can find a way around

@oliverio

I agree with Bas, the way I do “complex” date calculations with monday is to call an Integromat scenario.

I use a seperate board that contains the holidays by region/country. Then these dates populate a Google Sheet once a week. I then use it do the necessary calculations.

If you are looking for a simpler approach, some monday date functions can reference holidays. However, these features are not documented in monday. I have documented these somewhat here: monday’s hidden functions

Or, if you prefer, I also did a video on some of these functions, available here: monday Formulas - Part 4: The SECRET Functions - YouTube (the date functions with holidays starts at about 10:00)

Jim - Subscribe to The Monday Man
Watch Our Latest Video: monday.com Mirror Columns in Integromat - the Basics
Check out our monday apps, now in beta: The Monday Man Apps

This would be a really nice feature to have. We’re using Time Tracking columns on several boards to keep a record of our response times on specific tasks, but with no way to pause the tracker automatically at the end of the workday, the column is basically useless. If a task is generated ten minutes before close and is resolved five minutes after open, that should be fifteen minutes, not fifteen hours. I feel like this is a critical feature (could be an optional toggle on the column) and I’m really surprised there’s no simple way to make it happen.