Search code examples
excelgoogle-sheetsgoogle-sheets-formulagoogle-forms

How do I prevent the date from reformatting when new data populate into an excel sheet from Google form?

I have created a Google form and used an excel sheet to populate data that are entered into the form. When there is a new submission, the date formatting reverts to this: 5/10/2023 14:54:39. I want to have the dates of new responses format like this: May 10.

Sheet can be viewed here: https://docs.google.com/spreadsheets/d/1B-dP6GZqec7ONuXa89LRBNt-rnnHg18d7ihQXGKEIxU/

I'm still learning excel and have tried using an "ARRAYFORMULA" but I keep getting errors because I have just copy and pasted formulas I found online. Because I don't exactly understand what the formulas mean, I'm not sure how to adjust it specifically for my sheet.

Solution

  • To copy the timestamps and show them in the format you specified, put this formula in row 1 of a free column:

    =query(A1:A, "format A 'MMM d' ", 1)

    See query().