Due Date Formula

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

2 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…

1 Like

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

4 Likes

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

5 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

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

I am looking for something similar as well, but i want my resulting date to be before the imputed date. I try using a negative number but that does not seem to work.

Thanks for the help

1 Like

Not sure if this helps, but this function adds 2 days on to the date column column ‘Last Action’.
This also works if you put -2 in, it will then calculate the date 2 days prior to ‘Last Action’ date.

FORMAT_DATE(ADD_DAYS(FORMAT_DATE({Last Action}, “DD MMM”), 2), “DD MMM”)

Hello,

I don’t know if this thread is still active but i have a question regarding the formula dates.
I want to add the dates of the formula to the calendar but i cant manage to fix this.
Does anyone have a solution for this?

Thanks for the help!

1 Like

Hey @replaysam, thanks for asking this question!

I would recommend writing to support@monday.com so we can work with you to see how the formula column and dates can best be incorporated into your workflow.

1 Like