Due Date Formula

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!

6 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

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

3 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

1 Like

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

1 Like

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…

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

3 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

1 Like

You’re very welcome @AshleyWoods :slight_smile: :muscle:

Thank you for the kind words! :raised_hands:

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))

Screen Shot 2019-12-16 at 01.03.50

to minus a number of working days to a date
FORMAT_DATE(WORKDAY({AGREED DATE},7-1))

Screen Shot 2019-12-16 at 01.06.34

Hope this helps

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

@Van.ly

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