Un-representable Datetime Error (the added or subtracted value)

[William_Robins]

Hi Everyone,

I just got started into AppSheets and would love some help. I searched the existing forums and help menu, but have only found one other post about this error I am getting. I tried their solution and it doesn’t really seem to be the same situation.

I am getting the following error:

"error:the added or subtracted value results in an un-representable datetime. parameter name: months/n"

I went through all my tables, found all the “date” fields and clicked into the expression writer for formula and initial value and then hit the test button for each of these. I was able to identify the table where the error is happening. I have a table called “Reports” which includes three fields; a key, a ref, and a date. Each row includes a date (i.e. 1/1/2019, 2/1/2019, 3/1/209…thru 12/1/2025) This date is used as a reference to aggregate the financial information in another table by date so that I can use it to build some financial dashboards. Originally this column was working fine as a “real” column, but recently I switched it to a “virtual” column and that is when my problems started.

The error appears when I try to “Add” a financial transaction to my other table. Any thoughts? I am worried this could be an AppSheet bug, but I wanted to check to make sure it wasn’t due to my own doing. Your wisdom would be greatly appreciated!

[Steve]

What is the expression?

[alphacp]

Thanks for the help here all because this solved the same problem for me. I had a VC that contained the following formula:

EOMONTH([Start Date],[Duration]-1)+DAY([Start Date])

My app would not load and it is because as part of loading is is recalculating the VCs and hits a problem where [Start Date] was blank of one or more rows. I simply wrapped the formula in an IFS(ISNOTBLANK([Start Date]),.......) and all is good 

[William_Robins]

William_Robins:

1/1/2019, 2/1/2019, 3/1/209…thru 12/1/2025) T

So I am not 100% sure which expression it is, there are about 30 of them that run off of this date because it is a financial metrics dashboard and every card is a different calculation grouped by the date; however, one interesting thing is that when I use the app’s forms to add a new transaction it works, but when I try to use the upload csv file function it errors out.

[Joseph_Seddik]

William_Robins:

error:the added or subtracted value results in an un-representable datetime. parameter name: months/n"

I’ve seen this before, but not in AppSheet. Anyway, check for empty date columns, they would give this error.

Try to import your csv file in small chunks and see which one is causing the error. You might find a blank field hidden in the middle.

I hope it helps.

[William_Robins]

@KJS,

Appreciate the recommendations. I checked all of the Google sheet tables, there are no empty fields, cells, blank extra rows, I have checked trims and everything looking for extra spaces, etc. to no avail.

I have also only been trying to upload just 1 record with my csv and have started by adding one date field at a time to see at what point it is erroring out. It appears that it has something to do with a virtual column or expression, because I can add records manually through the app, but I just can’t upload them. I was beginning to wonder if there might be a formula that is receiving a “Date” but it needs a DateTime instead. I noticed that the error is says DateTime. All of my fields are Dates but I was curious if maybe I am using an expression that somehow requires a DateTime to resolve.

[Joseph_Seddik]

Before further jumping to conclusions:

Steve:

What is the expression?

[William_Robins · Accepted solution]

Hey, I finally figured out the issue after deleting each of my 70 virtual column expressions one at a time to see which expression was causing the issue. 

I had an expression that was trying to find the earliest date in a column of type 'date' filtered by a customer ID.  I was trying to find the date of the first order placed by each customer in a table with hundreds of orders with hundreds of customers by using a Min(Select table[date type column], where customerID = _this row (pseudo'ing here). Apparently, Min( ) doesn't work well with 'Date' Types and AppSheets isn't catching the error but rather is displaying the error directly from the server.

This definitely feels like an issue that would come up regularly enough that AppSheet could hopefully provide a stronger error prompt to help guide users to a resolution.

Thanks for everyones' help with this, hopefully it will help some future soul not go through the debugging pain I just felt!

[Joseph_Seddik]

Thanks much William! 

Would it be possible to share a sample of the csv file you were trying to upload please? Thank you!

[William_Robins]

Customer_Name

Contract_Description

Booking_Date

Start_Date

End_Date

Install_Months

Renewed

Cancel_Date

order_Type

is_new

RCV1

RCV2

RCV3

NRCV1

NRCV2

NRCV3

77c08a62

Sample Company 382 - New 2019 Contract

12/10/18

1/1/19

12/31/19

New

TRUE

16000

3000

15000

15000

8000

2000

0b908110

Sample Company 668 - New 2019 Contract

11/18/18

1/2/19

1/1/20

New

TRUE

10000

8000

18000

19000

8000

6000

19b29e54

Sample Company 325 - New 2019 Contract

12/5/18

1/3/19

1/2/21

New

TRUE

8000

2000

8000

4000

10000

7000

a5d00aa0

Sample Company 695 - New 2019 Contract

12/1/18

1/4/19

1/3/20

New

TRUE

1000

20000

2000

2000

11000

16000

[Joseph_Seddik]

I wanted to check the original file, not view its contents 🙂 

In any case, what I had seen is the same error in .NET + SQL environment and was caused by date falling out of range, which is either before 30-12-1899 or after 31-12-9999. A subtraction from an empty date column would give a date earlier than the minimum date. 

The last I can tell you is to check the locale in your csv against the table's locale. 

Other than that, MIN() along with SELECT() work perfectly in AppSheet on date columns. I'm using them. 

[leinharte]

I'd like to share my case.

◼︎ My app includes the table which has only one record.

◼︎ And I had a same error message like this.

I could not get any other explanation about this error.

▶︎ I just tried to fill the cell at google sheet directly. Like this.

Then the error was solved.

I hope this helps.