TIME FORMAT BUG, LOCALE SETTINGS ARE CORRECT

[Cameron_Wilson]

Hi I have a formula pulling times from a roster to auto-populate my sign in forms. this works however the time it pulls across has the following output: 

Sat Dec 30 1899 09:00:00 GMT+1000 (Australian Eastern Standard Time)

I Only require the following highlighted section:
 Sat Dec 30 1899 09:00:00 GMT+1000 (Australian Eastern Standard Time)

In the roster table the columns are set to type: time and locale is set to English:Australia
Due to the unexpected output I have had to change the format of the column in my sign in sheet to text, otherwise I get an invalid value when auto-filling the form if I select time.

Does anyone have any idea why this would be occurring, I'm completely stumped.

Thanks for any assistance.

[TeeSee1]

I have done a quick check on this as follows

TABLE1

id,

time: type time

TABLE2

id,

table1 id: ref

time: type time, Initial value (ANY(SELECT(TABLE1[time], [id] = [_THISROW].[table1 id]))

On the Add FORM of TABLE2, table1 id is selected, which automatically populates the time col pulling the value of the time of the row from TABLE1 specified by table1 id.

Everything works. (Just pulls time value)

How is yours set up?

[Cameron_Wilson]

Hi here is my setup:
TABLE 1 = ROSTER (each row is an individual shift, for an individual employee, at one of our many stores)

Column: Shift Start, Data type = time 

TABLE2 = SIGN IN (User inputs staff id, or scans their staff id qr code and it autopopulates all fields except for fields which are variable, eg. (health status, temp check.)

Column = Start Of Shift, Data type = time(If set to time it gets an invalid input as the format of the time is incorrect.)
(formula used to auto-populate column) = https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/auto-populate-form-with-Rostered-Start-Time/m-p...

Thanks a lot for your assistance.

I'm considering for now using  Left and Right to reformat the provided output, but if you have a better suggestion i would be very grateful.

[Cameron_Wilson]

I have also tried EXTRACTTIMES on the formula in my expressions for the auto-populate. The expression looks like: EXTRACTTIMES(SELECT(ROSTER[SHIFT START],AND(([_THISROW].[Date]=[Date]),([_THISROW].[ID NUMBER] = [STAFF ID]))))
HOWEVER THIS SAYS FUNCTION EXTRACT HAS INVALID INPUT, Yet when I do not have the extract function, the rest of the function provides a text string, so i'm very confused.

[TeeSee1]

Are you using Google Spreadsheet as your data storage?

You sure your time column in ROSTER has the right format there?

[Cameron_Wilson]

Hi Yes my formatting of the time columns on both the rosters table and the corresponding sign in form table are set to time.

I am using google sheets.

[Steve]

Where are you seeing the unwanted output? A screenshot would be helpful.

Where is your time zone set? A screenshot would be helpful.

[Cameron_Wilson]

Hi Steve,

Please see attached screenshots.

Unwanted output in the google spreadsheet table for sign in form, under the start of shift or end of shift column.

The issue I seem to be having is the select formula is only returning a list of an individual item or a text string rather than just the time. 

Any assistance would be appreciated. Once again, I'm close but missing something.

I need it to be in time  format as I will use this data in further formulas to see if staff are late, early, calculate worked hours vs rostered, etc.

Unwanted OutputSIGN IN TABLE DATA LOCALEROSTER TABLE DATA LOCALEGOOGLE SPREADSHEET DATA LOCALE

[TeeSee1]

Could you try re-setting the gSheet format to the correct ones? (copy the format of the correct ones to be applied to the entire column)

[Cameron_Wilson]

Hi @TeeSee1 , I have done this, it is not the issue.
The issue is the formula providing a result which is a list, the only data types i can set for the column are list (If i place it in a virtual column) or text, which provides the undesired output mentioned above.

I think i need to alter the Formula :SELECT(ROSTER[SHIFT START],AND(([_THISROW].[Date]=[Date]),([_THISROW].[ID NUMBER HIDDEN] = [STAFF ID])))

I need to have the result provide the individual result and not a list or text string, i just do not know how to it/everything I've tried has failed.

[TeeSee1 · Accepted solution]

If you want a value rather than a list, simply prepend ANY(SELECT(...))

See if this works

[Cameron_Wilson]

Thanks @TeeSee1  I thought I tried it earlier but must have had something slightly off at the time.

That works like a charm.

[Cameron_Wilson]

Also for further information, I have tried filling out the form from within the editor on google chrome macbook osx, and also from the appsheet app on ios, i get the same result. I was thinking maybe it's what the locale on the device was sending as output when requesting the time.