DateTime Calculations

Charles2 · 10-14-2019 04:02 AM

Hi,
I’m new to Appsheet and am having trouble calculating a future date based on a given date and device type (e.g. If Notebook then decommission date is in-service date + 3.5 years (3yrs 6 months).
I’ve tried several different possibilities but usually get “The expression is valid but its result type ‘Date’ is not one of the expected types: Duration”.
What am I doing wrong?

PS: You really need more TAGS to fine-tune a search. I can look for expressions but not narrow it down further with *Date Time. That means I can search through ALL questions regarding expressions and hope I find something helpful…

Suvrutt_Gurjar

Hi @Charles,

You may wish to take a look at the type of column you are using to populate the result of your calculation. It sounds that the expression you are using is correct but probably the column type is duration type?

On search you may wish to give your keywords in the search field itself. Also you could access some very useful AppSheet documentation athttps://www.appsheet.com/Support

For the query in your current post, I request you to take a look at following article, just in case you have not

Charles2

Hi @Suvrutt_Gurjar,
Thanks for the quick reply. I just realized myself that the type was wrong. I guess that I’ve been looking at this too long.
I still have problems with the calculation, though.
I found a different way to achieve what I want but I don’t understand how it is calculating.
My formula is DATE(EOMONTH(3/11/2019), 42). It should give me 9/30/2022 (3.5 years from the given date). What I get is 7/31/0004.
Any ideas or did I use it wrong again?

Suvrutt_Gurjar

Hi @Charles,

You are welcome.

Can you please try just EOMONTH(“11/03/2019”, 42)

This is so because the EOMONTH() itself retrurns a date, so as per my understanding further typecasting into date is not required.

Charles2

UPDATE:

If I use a date Constant (3/11/2019) the formula calculates correctly. If I use a column variable - in my case “In Service Date” - it shows the answer I referred to above.

You can test this by using an Excel sheet as the source with a column formatted as SHORT DATE and enter 03/11/2019 as the cell value.
Add a virtual column with the formula EOMONTH(Column_Name, 42) and test.

Suvrutt_Gurjar

Hi @Charles,

Please try with an expression something like

EOMONTH([InServiceDate], 42)

Here [InServiceDate] is a date type column containing dates.

Suvrutt_Gurjar

Hi @Charles,
I tested the expression mentioned in my above post. It works for me. Could you please mention what error you are getting.

Steve

In all likelihood, 3/11/2019 is being interpreted as an arithmetic expresssion: 3 divided by 11 divided by 2019. Literal Date (and DateTime and Time) values should be quoted in AppSheet expressions: DATE(EOMONTH("3/11/2019", 42)).

Charles2

Hi @Steve and @Suvrutt_Gurjar,
Thanks for the reply. I realized that in the course of my chat with @Suvrutt_Gurjar. We have progressed to EOMonth(In Service, 42). The formula editor says this is fine but when I click Test it shows the date as 07/31/0004

BTW: The red “2” is from a Plugin that I use (Grammerly).

Suvrutt_Gurjar

Hi @Charles,

Thanks for screen shots. Is “In Service” a column?

Then in that case, as indicated earlier, please use the expression EOMONTH([In Service], 42)

In AppSheet expressions ,a column value needs to be shown as [Column] where “Column” is the column name.

Charles2

Good morning @Suvrutt_Gurjar (it’s morning here),
That is the formula that I started with and the pics are from this morning after I re-entered the formula.

Sorry about the incorrect column name (In Service Date)

Charles2

@Suvrutt_Gurjar as you can see here it makes no difference if the column is pre-filled - the second row - or empty. The strange date remains the same.

Strangly enough if I give it an initial value expression of **EOMONTH(Today(),0) it calculates correctly.

Suvrutt_Gurjar

Hi @Charles,

Thank you for the updates. In the picture , where you are getting error ,does it contain the expression EOMONTH([In Service], 42)

Your column name in the picture is [In Service] and not [In Service Date] as you mentioned in previous post. Request you to check that part.

Could you please check with expression EOMONTH([In Service], 42)

I could get correct results with my testing. I have named the date column [In Service] exactly like yours

Charles2

@Suvrutt_Gurjar,
How embarrassing! I have been wasting your time simply because I forgot the brackets around the column name!
Please excuse this. Next time I will check everything 10 times before I ask for help.

Thank you so very much for your patience and time.

Suvrutt_Gurjar

Hi @Charles,

No problem.You are welcome. We all unknowingly err in the beginning, till the time we get used to any new system.

All the best with your app.