Calculate a date that is always "next month"

Question

Hello!

Every time a survey is submitted, I need to calculate a date that is always 24 days after the 1st day of the next month. (the expiration date)

I managed to do the following but I need to update the date in yellow every month, and I am afraid if I forget only once everything will go wrong.

Is there a way I can set a "next month" rule?

Thanks!

AndrewMcDonagh · Answer

Did you ever get this to work?

I am having Manager submit a Survey Entry reporting about the previous months actions, where they don't choose a day - just the month/year. So submitted on March 4th - the "date" would be Feb 2024, and stored as 2/1/2024.

I would really like to somehow store it as 3/1/2024 even though we are talking about February - because it is summarizing the previous month.

When I get to Dashboards - I want to compare this most recent month with the one before it. So I would filter entries to "This Month" and then subtract "Last Month" on all the fields we collected info on.

Being able to let the Manager select "Feb 2024" as the month they reported on, but store a date of 3/1/2024 would be great! thanks for any help!

PanGIS · Answer

@JayantaPoddar  in case you have 5 minutes to spare, here attached a xls with three samples.

The first one is the one that works.

Thanks!

test_Date.xlsx

PanGIS · Answer

Thank you @JayantaPoddar

You got exactly what I need but I am struggling with the function.

is that "eomonth" function supported?

If I replace with this:

if(1=1, 'EOMONTH(TODAY(),0)+1', if(1=0, '0000-00-00','1111-11-11'))

The calculation results in a string: 'EOMONTH(TODAY(),0)+1' not a date

this means that when I try to add 24 days to my date field field I get an error.

(also tried to bind the field as date but nothing).

I also tried the following, instead of the previous string:

date(decimal-date-time(eomonth(${inspection_Date},0)+1)) , not sure if it make sense, but I get a error in calculating the expression.

I am attaching the xls as soon as I get back to my desk.