Hello, so I want to tag @Koichi_Tsuji and @Steve on this since I saw his original post having the same error as I’m getting. (Hope you don’t mind)
I’m getting the same error whenever I do EOMONTH( [dateplanted], -1 ) where the offset is a negative number.
Funny thing is, if I use EOMONTH as a part of a duration calculation (calculating days until end of month) and still use a negative offset, it runs ok.
A bug?
So this works:
( 5 + [Gantt Scale if over 6 months] * FLOOR(HOUR(TODAY() - EOMONTH(TODAY(),-8)) / 24) )
But this doesn’t:
TEXT(EOMONTH([MyPlants_DatePlanted], -6), "mmm")
Of course I can get around the last expression because I only need the text month, by adding 6 months instead of subtracting, but the point is, why is it not working as is?
My guess is you are probably facing locale issue somewhere in the expression chain. For example the the day in the date may be taken as a month and if day of the date is greater than 18, maybe it is giving the said error.
Could you please test how the expression works for dates like 10/05/2025 , 12/08/2025 ( first set) and second set ( 25/05/2025, 30/09/2025) . All these suggested dates are in (dd/mm/yyyy) format.
Hi @Suvrutt_Gurjar , I think you’re correct (sorry am unsure whether marking your answer as correct already will allow me to keep replying?)
TEXT(EOMONTH(“10/05/2025”, -5), “mmm”) gives May
TEXT(EOMONTH(“12/08/2025”, -5), “mmm”) gives Jul
TEXT(EOMONTH(“25/05/2025”, -5), “mmm”) and TEXT(EOMONTH(“30/09/2025”, -5), “mmm”) both give Error: The added or subtracted value results in an un-representable DateTime. (Parameter ‘months’)
So I started this app on a laptop with dd/mm/yyyy. However the datetime column in question on google sheets doesn’t have a number format set. I’m now working on a Filipino laptop with mm/dd/yyyy format. My datetime column looks like dd/mm/yyyy in google sheets.
How can I get my EOMONTH to work, some sort of conversion in the expression ?
I’ve tried TEXT(“09/30/2025”,“dd/mm/yyyy”) which says OK but when I do TEXT(“30/09/2025”,“dd/mm/yyyy”) it errors: TEXT function with two arguments requires a temporal type and text representing a date format
I thought I might be able to force the format such as
It sounds that you started the app work in laptop with different date locale and now using a laptop with different locale could be causing issues?
I believe you could ensure that the date format locale in entire chain is identical: backend sheet locale, AppSheet table locale setting, device or browser setting.
I believe just changing the date format using an AppSheet expression may not work with reliability across all the instances.
A literalDate value in an expression must be written in the US format, mm/dd/yyyy, regardless of any locale set or used anywhere else. “30/09/2025” is not a valid Date value because there is no month 30. Dates in your data source should be structured as appropriate for the source’s locale. A date entered into the app itself should be structured according to the device’s locale. But a date entered literally into an expression MUST be in the US format, mm/dd/yyyy: "09/30/2025".
I also suggest that instead of testing the suggested dates independently in the format TEXT(“30/09/2025”,“dd/mm/yyyy”) by manually entering various dates, please test these dates by entering in the app in the column [MyPlants_DatePlanted] and test how those behave with the EOMONTH () expression in the test pane. This will confirm if our assumption is correct of day of the month being treated as month value.
I ended up using EOMONTH with positive offsets instead in order to avoid further testing. haha, it was easier.
Ok, so now with your prompting I’ve tried testing as you suggested, set up a view with quick edit. Because [DatePlanted] is a date it forces me to use my locale format of dd/mm/yyyy in a form/quickedit view.
If I save the app at this point it gives error “Unable to fetch app definition. Error: The added or subtracted value results in an un-representable DateTime”.
So I have to remove the expression and re-save etc.
My locale is Australia so its dd/mm/yyyy (Grams and ounces rows)
If I add it as mm/dd/yyyy it shows it as text (kilograms and pounds row)
I resync Appsheet to pickup the new values and then add two virtual columns, one using EOMONTH([dateplanted],-2) as a date column, the other including it in a text string:
Funnily enough, regardless of the way it was entered in Google Sheets, it works. Notice that [DatePlanted] column always displays as dd/mm/yyyy but if it’s used in a text string it shows it as mm/dd/yyyy.
But as soon as I save the app it ERRORS. So I can’t use it. All this was tested on our Filipino laptop which has mm/dd/yyyy settings (for the rest of the family’s sake, not mine, haha).
Then I go back to my old Aussie laptop and it’s behaviour is EXACTLY the same.
I don’t know if this answers your idea of it being a locale problem or not? Maybe later if I get time I’ll make a new standalone simple app and retry all this.
Thank you @leonsteber for all the tests. I believe it may be advisable to test it in a seprate test app as you mentioned. But frankly, at this moment, based on your recent test results also, it is unclear why exactly EOMONTH() fails in your case.
In most posts, while suggesting a solution, I myself would have tested it. But this dates issue is so locale dependent most of the times. So, I believe I may not be able to replicate your exact test environment in spite of you mentioning locales you have configured etc.
As such I believe we can pursue this only if you have time to keep testing. Alternatively, we can take a pause here as you have already found a workaround solution.
I will definitely post back in case I come across a convincing reason in future for this EOMONTH() function behavior.
