CODEWITHSUNDEEP
google-sheets
t0m3k
t0m3k

Reputation: 317

ArrayFormula displays dates improperly. How can I fix this?

Form Responses 1 looks like this: 6/1/2020 13:59:11 On my second sheet, it turns it into this: 43983.582763588

This is the formula I'm using: 

=ArrayFormula(('Form Responses 1'!A2:A&" "))

What's going on here?! :| Please help. I've tried converting to plain text on Response Sheet 1 and it fixed it temporarily. However, new entries still turn into the weird format.

Upvotes: 0

Views: 253

Answers (1)

Rafa Guillermo
Rafa Guillermo

Reputation: 15357

Answer:

Days are stored in Google Sheets as the number of days since 1st January 1900. You just need to change your Sheet's display format and remove the &" ".

Fix:

Change your formula to the following:

=ArrayFormula(TO_DATE('Form Responses 1'!A2:A))

Then, highlight the column for which this Arrayformula will enter data, follow the Format > Number > More formats > More date and time formats and fill out the opened modal to look like this:

enter image description here

Using the dropdown arrow on the right (v) to select each of the Month/Day/Year Hour:Minute:Second elements you like.

Afterwards your dates should look like this:

enter image description here

Upvotes: 1

Related Questions