Hi ! Having troubles with phrasing (i think....

(Michael Avenilov) #1

Hi !

Having troubles with phrasing (i think…),

maybe can get some tips from you guys over here :smiley:

Based on a selection from a list of values, if one specific value is chosen, i need the [date] value of the current row become :

Next month (from today) on the 15th DAY of the current YEAR.

Choose: A\B\C\D.

IF: B. then change [DATE] value to: today + 1 month, 15th day, current year, ELSE [Date] = today.

Any chance to achieve that result?

(Reza Raoofi) #2

Well, you can’t go to exactly to next month, but you can add 30 or 31 days to today by an expression like this: TODAY()+30 Then you could extract month and year from that date by MONTH() and YEAR() functions, and construct the particular 15th of the next month by concatenating all pieces together.

(Michael Avenilov) #3

@RezaRaoofi hi, i think it might work.

didnt quite understand how texacly to EXTRACT the values MONTH and YEAR

(Michael Avenilov) #4

Thank you all . your tips are priceless :smiley:

(Gregory Diana) #5

Yes it is available in Appsheet, but undocumented, and its usage is the same as in Google Sheets. It appears under Expression Builder in Appsheet.

Appsheet support probably needs to note this and update the documentation for this function.


support.google.com - EOMONTH - Docs Editors Help

EOMONTH - Docs Editors Help support.google.com

(Michael Avenilov) #6

@Gregory_Diana thank you for the link! useful!

(Reza Raoofi) #7

CONCATENATE ( MONTH(TODAY()+30), “/15/”,…) You finish it yourself! :wink:

(Michael Avenilov) #8

@RezaRaoofi thanks! but…neither im still missing the right phrase, neither typing the formula in wrong column or type qualifier because after completing a form. The date column remains blank in the spreadsheet. :frowning:

if( contains([Тип оплаты], “Кредитка”), [_THIS] = CONCATENATE (MONTH(TODAY()+30),"\15",“2019”) , [_THIS] = today() )

this is what ive managed to combine…

(Gregory Diana) #9

@Michael Avenilov

It appears the first CONCATENATE returns text and the second CONCATENATE date. This causes the IF statement to return two different types (text and date).

Suggest you wrap the first concatenate as [_THIS] = DATE( CONCATENATE (MONTH(TODAY()+30),"\15",“2019”)). This will cause both statements to return both results as DATE and should resolve the problem.

if( contains([Тип оплаты], “Кредитка”), [_THIS] = DATE( CONCATENATE (MONTH(TODAY()+30),"\15",“2019”)) , [_THIS] = today() )

(Steven Coile) #10

To calculate the 15th of next month:

(EOMONTH(TODAY(), 0) + 15)

(Michael Avenilov) #11

@Gregory_Diana tried to rephrase as you said, still not getting the date in the column :frowning: attaching a screen shot of the column with working form showing date value and with the formula

(Michael Avenilov) #12

+Steve Coile I had a feeling this function (eomonth) may be the solution! didnt find any info about it any whre… anyway, it worked!!! thank you and thanks all for your helping :smiley:

(Gregory Diana) #13

@Michael Avenilov Hi I think I got mixed up with Appsheet and Google Sheets DATE function. As +Steve Coile noted the EOMONTH function is the correct one to use

if( contains([Тип оплаты], “Кредитка”), [_THIS] = EOMONTH(TODAY(),0)+15 , [_THIS] = today() )

(Reza Raoofi) #14

+Steve Coile Michael is right, EOMONTH() function seems to be undocumented; I did not find it in Date and Time expressions: https://intercom.help/appsheet/expressions/expression-types/date-and-time-expressions

But it has been used in examples, and there are links to it in documentation for other functions but the link opens a page like this: