Formatting Array date & time from Google Sheets

I have an array formula active in many of my columns in Google sheet.

When an action adds a new row to the sheet, the date and time is correct, but formatted wrongly. It is appearing as numbers, like 44231 instead of 2/4/2021 and 0.9375 instead of 10:30PM. The issue is that when it appears as a number instead of date/time in Google sheets, Glide doesn’t recognize it as a date/time.

How do I force my array formulas to formatt correctly as dat and time?

format whole column in to a date format

I did that already. But when a new row gets added, it’s formatted as a number instead of a date. The format seems to reset to numbers when a new row is added, which is what led to the screenshot I attached.

did you format glide and GS? or just GS? you can add in your array formula (text( A1:A,"m/dd/yyyy"))

I formatted GS. But I just figured out the issue.

I have Glide inputting timestamp into a column, like this 2021-08-23T09:00:00.000Z
I am using the INT function in Google Sheet to extract the date in that column to another column because I want to perfom a date calculation.

In essence, what I need is a GS formula that can convert 44231 to 2/8/2021 without me having to go manually format the whole column? I can’t use the TEXT function either because that converts it to a string I can’t do math calculations with.

  1. use text formula for that
  2. delete last “” from your formula, just have ),))}

Hmmmm, got an error after trying that.

The last “” is a part of my IF formula. That was me telling the IF function to return a blank result if the condition was FALSE.

ARRAYFORMULA(IF(I2:I<>"",(text( I2:I,"m/dd/yyyy")),))}

1 Like

Thanks, this worked.
Really appreciate you!!!

1 Like