Hi! I’m hoping someone can offer some assistance. I want to set a due date based upon another date. For example, when the Board approves a hire, I want to input the Board approval date and have the due date for the Work Record creation to be 7 days (excluding weekends) after the Board approval date. Does anyone know a formula that could assist with this? I’ve used an automation previously, but I had to have another column that I put the number of days in - in this case 7 and it did not exclude weekends. Thank you all in advance for any support or ideas you can offer!
9 Likes
Hey Ashley,
Ben here, happy to help!
Unfortunately, after quite a bit of time trying out different formula’s, I do not believe that this is possible. Although this is a really interesting use-case for the formula column. Having said that, I want to report this as a feature request. I believe that it would be more efficient, and effective to add this as a feature request for the previous automation that you used.
Please let us know if there is anything else that we can do for you!
Best,
Ben
4 Likes
We are looking for something similar!
Due Date = Date +/- Number of Business Days.
Example Use Case:
We have a list of the events we attend throughout the year, with the same basic workflow and internal admin process. We have a Due Date for Post Event Admin Tasks, manually set to 3 days after Event Date. It would be amazing to have an automation to do this for us!
There is an automation that we tried for this, but it does not work well. Set’s Due Date based on Date but requires a Column with Numbers in it. It’s quite clunky and there were too many situations where the automation did not work as expected.
4 Likes
Hey there,
Thanks so much for explaining your use-case. I have added this to the previous feature request that I submitted. We will keep you updated with the process.
I also want to thank you for taking the time to write to us. We focus our roadmap significantly on the feedback that we receive from our users, and so are always excited to hear your thoughts. You are helping us build the strongest platform our there!
Best,
Ben
2 Likes
Hey all,
Our amazing customer success manager @Scott-monday.com, stumbled across this post, and worked on an incredible formula for this specific use-case! If you see him in the streets, give him a smile and maybe a huge high-five!
RIGHT(LEFT(CONCATENATE(WORKDAY({Date}, 8)), 15), 11)
If you’re interested in the rationale, i’m going to explain it below. If the formula was enough, and you want to copy and paste it, you should be all set.
the 8 is what adds 7 days to the day (which is a bit confusing). In order to add 7 days, we need to use 8 because the formula is counting “today” as a 1 of those days
in order to utilize either the LEFT() or RIGHT() functions, we need to first use CONCATENATE() to turn the date output into a string we can modify with LEFT() and RIGHT(). If we don’t first use CONCATENATE(), then LEFT() and RIGHT() won’t work
The 11 and 15 are from the LEFT() and RIGHT() functions. We’re simply using those functions to clean up how the date is displayed
3 Likes
This would work, except the new date that is generated by the formula cannot be used in “Deadline Mode”–unless I’m missing something. If not, that would be a great feature to add, because most of the functionality of the Date column we use relates to it being a deadline…
2 Likes
Hey there James,
You’re 100 percent correct. I have submitted this as a feature request, so our product team will be taking a look into developing it. This is a formula that we created as a work-around in the meantime. Does that make sense?
Best,
Ben
10 Likes
This work around is perfect! @Scott-monday.com THANK YOU! And, Ben thank you for submitting the feature request, because I am also looking for this formula to be utilized as a deadline for MANY boards that we have.
-Ashley
5 Likes
You’re very welcome @AshleyWoods 
Thank you for the kind words! 
Try this formula
Note - Agreed Date being the name of your column with the date
to add a number of working days to a date
FORMAT_DATE(WORKDAY({AGREED DATE},7))
to minus a number of working days to a date
FORMAT_DATE(WORKDAY({AGREED DATE},7-1))
Hope this helps
6 Likes
Great, we will try this out!
I haven’t found this formula any where else (inside formula editor and in the articles)
How can I know if there is any hidden formula else?
Hey Van.ly!
Welcome to the community and extension of the monday.com family! What do you mean?
All functions are available for all to view. For specific results, you may need to play with different functions to make sure you are using the right one. You can watch our webinar that goes in-depth on this column, click here to see it.
Are you looking for a specific result when using this formula?
1 Like
Hi Bradley,
I mean I can’t find this function in the page link I mentioned. Also I can’t find it in the dropdown list of formula column. I can only find it through Google or community search (with “networkdays” keyword). I’ve just wondered how many functions else is hidden else where.
Hey @Van.ly
Thanks for highlighting that, I’m checking with my team as these might the only functions that are not listed on the dropdown menu. And we’ll be updating the article I sent with new functions that have not been added yet like:
Logical functions :
SWITCH
Checks if a condition on a specific value is met, if so, returns the result of that value, o/w returns the default result (if exists). The pattern is: SWITCH({Column}, “val1”, “result1”, [“val2”, “result2”], …, [“default”])
Examples:
SWITCH({Priority}, “High”, 3, “Medium”, 2, “Low”, 1, 0) => 2 (in case priority is “Medium”)
Numeric functions
COUNT
Counts the number of numerical items.
Examples:
COUNT(1, 2, “a”) => 2
Date and time functions
WORKDAYS
Returns the number of working days between the two dates.
Examples:
WORKDAYS({TO_DATE}, {FROM_DATE}) => working days between dates in days
FORMAT_DATE
Returns a formatted date.
Examples:
FORMAT_DATE(TODAY()) => “Feb 16, 2020” (using default format)
FORMAT_DATE(TODAY(), “YYYY-MM-DD”) => “2020-02-16”
FORMAT_DATE(TODAY(), “dddd, MMMM Do YYYY”) => Sunday, February 16th 2020
ADD_DAYS
Adds days to the given date. Returns the new date.
Examples:
ADD_DAYS(“2019-01-20”, 5)
ADD_DAYS(TODAY(), 5)
FORMAT_DATE(ADD_DAYS(“2019-01-20”, 5) => "2019-01-25
SUBTRACT_DAYS
Subtract days from the given date. Returns the new date.
Examples:
SUBTRACT_DAYS(“2019-01-20”, 5)
SUBTRACT_DAYS(TODAY(), 5)
FORMAT_DATE(SUBTRACT_DAYS(“2019-01-20”, 5) => "2019-01-15
HOURS_DIFF
Returns the difference between two hour columns
Examples:
HOURS_DIFF(“23:00”, “20:00”) => 03:00
2 Likes
Thank @bradley! I see that WORKDAYS doesn’t have argument for Holiday. How can I exclude Holidays?
At this time platform does not have a setting for holidays or blackout days, so there is not a simple way to exclude the holidays that your team observe.
We hope to add that to the admin settings similar to the option of first day of the week.
1 Like
@bradley: I think about a board like Holiday, and WORKDAYS formula can have arguments like WORKDAYS(…, “Board name”, “Date column name”). Most system has multible calendars (holiday calendar, leave calendar). A distributed company may also have different holiday calendar at different sites. So far I think that using a board for holiday is better than a centralized holiday calendar.
Hi! I want to second this feature request.
If we attended a work event outside of work hours, we can apply for compensation leave within X days of event date. I’d like to set up the “claim leave by” column as a deadline and remind people not to forfeit their leaves. Work-Life harmony!
Hey @Holing thanks for the feedback! I will be sure to pass this along to our team as we are working towards working on the formula column in terms of capabilties!