Today() in Array from Excel Australia

Mordan74 · 05-22-2020 06:13 AM

Hi there.
I have an excel formula which is an array formula that looks up if the date is less than or equal to today(). If so it makes the result 0. If not then for the first record it pulls the current bank balance and deducts the first transaction. It then continues down the list removing transactions from the previous cells balance and creates a running sum.
It works great in Excel and appsheet however it seems to be using US time so the date until about halfway through the day is the previous day since the US is 12 hours behind Australia.
This means my app only works in the afternoon which is annoying.
If I use Today() in Appsheet in a virtual column it returns the correct day. If I have a field in Excel with just Today() in it then I get the day in the US.
I have set the table Locale to English (Australia).
The excel formula is:
{=IF(B2<=TODAY(),0,IF(D1=0,INDIRECT(“Transactions!$G”&COUNT(Transactions!A:A)+1)+AppSheetForecastSheet!C2,AppSheetForecastSheet!D1+AppSheetForecastSheet!C2))}

This translates to the following in Appsheet
=ArrayFormula(IF(RC[-2]=TODAY(),0,IF(R[-1]C=0,INDIRECT(“Transactions!$G”&COUNT([-3]:C[-3])+1)+AppSheetForecastSheet!RC[-1],AppSheetForecastSheet!Transactions!CR[-1]C+AppSheetForecastSheet!RC[-1])))

This all seems correct.

Austin

If this is a constant then you could use a DATE(NOW()-time difference) in the appropriate area.
Turn your today into datetime then correct it then change it back to a date field.

Mordan74

Yeah I did think about that, it is ugly but might work. The only issue will be if they fix the issue it will then be out by the time factor I had to enter. It should be obvious when that happens though.
It would be nice to not have to use a workaround for a standard function. Myabe i’m doing something wrong.
Inside the blue circle Appsheet is looking at a field in Excel that has the formula Today()
Inside the Red circle is a virtual column using Today() in appsheet.
All my regional settings, calendar, date and time on the laptop are set correctly.

Mordan74

So the time it sees when you use Today() in your spreadsheet is UTC. Using Today() in a virtual column sends you your system date.
I’m sure this is a bug.
For now I have used Now()+0.416 in the spreadsheet and it seems to work.
I do use the spreadsheet though so now this will throw me off at times. It would be great to have a solution for this.

Austin

There is a UTCNOW() function that you might give a try if you know that excel is in UTC. they might not be the time you need but they would both be synced together so could make a workaround.