Calculate with month in formula column

Platform discussions Special workflows & use cases
1

Hello, I would like to calculate an end date for a contract, but I always deviate by a few days. I use the following formula calculation:

SWITCH({CoP},36,1095,12,365,6,183,3,92,2,61,1,31,0))

The CoP variable is given in months.

For example:
3-year contract has a CoP of 36 months aka 3*12 months =1095 days
1 year contract is a CoP of 12 months aka 365 days
1/2 year contract a CoP of 6 months aka 183 days and so on.

In order for me to calculate a contract end date, I proceed as follows:
I calculate the months into days with: SWITCH({CoP},36,1095,12,365,6,183,0)) and then I use ADD_Days( ) to calculate the end date. Problem: The end date can never be calculated exactly because each month has different days.

Can you show me an example that, for a given date, e.g. 15.03.2022, calculates 18 months ahead, i.e. 15.09.2023.

Thank you very much in advance !

2

@Honor

If your given date is in a column {Date}, this will give the new date:
FORMAT_DATE(EDATE(FORMAT_DATE({Date}), {CoP}))

The inner FORMAT_DATE() eliminates time zone issues.

Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: Creating subitems using Make/Integromat The Monday Man (EASY) Way
Contact me directly here: Contact – The Monday Man

3

Hi Jim,
Thank you very much for your help. I didn’t know there were hidden functions :wink: I’ve also read a special post from you about hidden functions.

Unfortunately I get errors ! I used a date calendar object, see screenshot and use
your formula:

 FORMAT_DATE(EDATE(FORMAT_DATE({Heute}), {CoP}))

The result is not continuously filled, although a (calendar) date is always selected.

image
image370×504 21.4 KB

image
image394×578 23.4 KB

4

@Honor

The issue is that FORMAT_DATE() apparently can SPEAK German but doesn’t UNDERSTAND German. I would consider this a bug. It might be helpful to submit the issue to monday support.

Here is a workaround:

FORMAT_DATE(EDATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMAT_DATE({Heute}),"Dez.", "Dec"),"März", "Mar"),"Okt.", "Oct"),"Mai", "May"), {CoP}))

Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: Creating subitems using Make/Integromat The Monday Man (EASY) Way
Contact me directly here: Contact – The Monday Man

1 Like
5

Thanks a million for your help !

Who knows where else such traps lurk :wink: As a beginner, I would never have found it on my own.
I thought I was too stupid to do it properly. Will report this obvious bug to Monday.com.
Your example works perfectly. You are just great !

Thanks Jim !

1 Like
6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.