Date Time Formula - datetime\month(datetime\today())+2

[dreginald]

Wanted to know that if I have used a formula datetime\month(datetime\today())+2, will it denote January of 2023 in the Month of November 2022?

[Jakub Grufik]

Nah, this one will return "13", you can always try to print the output in Administration -> Formula Sandbox

Just put there output\printLine(datetime\month(datetime\today())+ 2) and you will see the output at the bottom

[dreginald]

I have created a report for the sales forecast for January using this formula on the Close Date in Opportunities. It was working fine till last month and now I need to change the formula. Any alternate solution so that I do not have to edit the formula every month?

[esforim]

Have you already use the dateformat field? (or can't you use it?)

https://docs.espocrm.com/administrat...datetimeformat

[dreginald]

I am already using it.
But when we add +1 or + 2 months, it will not work if it is end of the year. Similarly if we add -1 or -2 months, it will not work in the beginning of the year

[esforim]

Ah that made sense! December coming soon.

Unfortunately I haven't delve into this issue yet so no solution or thread I'm aware of to give you some insight. You probably have to look through the formula/documents and see if there anything that potential give you a "light bulb" moment.

[lazovic]

Hi dreginald,

This formula will work as expected:

datetime\addMonths(datetime\today(), 2);

[dreginald]

Tried and its not working

[Jakub Grufik]

this one works perfectly

[emillod]

You can use our extension Custom Formulas. Here you have video which shows how to use our extension: https://www.youtube.com/watch?v=9PMxd2A5DO4
Here you have specific formula which allow to add days, weeks, months, years. https://docs.dubas.pro/extensions/cu...las/#dubasdate

[dreginald]

Thats Great. Will try it

[lazovic]

dreginald,

It is very strange that the formula I proposed does not work for you. I just tested it in my test instance, and the date is set correctly.

Could you show on the screenshots exactly how your formula looks like? As well as the settings of the report itself. This will make it easier to help you with this problem.

[dreginald]

@iazovic

I tried to create a report in Proposal Stage with Close date after two months and used complex expression using formual greater than (datetime\month(datetime\today())+ 2)

In October 2022, the report showed the opportunities with Close Date of December 2022.

But in November, it should show the Opportunities with Close Date of Jan 2023 and above. But it is showing NIL.

I changed the formula as you suggested and it is still showing the same and I have opportunities closing in Jan in Proposal stage.

Screenshot attached

[item]

hi,

try this :

datetime\addMonths(datetime\today(), 2) % 12; 

% - numeric modulo

you can try too with : complex-expression

https://docs.espocrm.com/user-guide/reports/#complex-expression

[Jakub Grufik]

dreginald hey, as you are referring to the MONTH of the CloseDate in your filter I think you should use this formula instead:

datetime\month(datetime\addMonths(datetime\today(), 2))

Let us know if this works please.