# Formatting Array date & time from Google Sheets **URL:** \ **Category:** Ask for Help\ **Created:** [August 22, 2021, 8:07pm UTC](https://community.glideapps.com/t/formatting-array-date-time-from-google-sheets/30800 "2021-08-22T20:07:03Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Dolapo\_Amusan](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/dolapo_amusan/32/16764_2.png) [@Dolapo\_Amusan](https://community.glideapps.com/u/Dolapo_Amusan)\ **Post date:** [August 22, 2021, 8:07pm UTC](https://community.glideapps.com/t/formatting-array-date-time-from-google-sheets/30800/1 "2021-08-22T20:07:03Z")
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? ![Screenshot 2021-08-22 at 19.05.37](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/9/0/90e23d39e83d191b5af917e6ecd0af13ffe85ccf.png) ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [August 22, 2021, 8:10pm UTC](https://community.glideapps.com/t/formatting-array-date-time-from-google-sheets/30800/2 "2021-08-22T20:10:38Z")
format whole column in to a date format ---
**Author:** ![Dolapo\_Amusan](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/dolapo_amusan/32/16764_2.png) [@Dolapo\_Amusan](https://community.glideapps.com/u/Dolapo_Amusan)\ **Post date:** [August 22, 2021, 9:02pm UTC](https://community.glideapps.com/t/formatting-array-date-time-from-google-sheets/30800/3 "2021-08-22T21:02:28Z")
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. ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [August 22, 2021, 9:09pm UTC](https://community.glideapps.com/t/formatting-array-date-time-from-google-sheets/30800/4 "2021-08-22T21:09:23Z")
did you format glide and GS? or just GS? you can add in your array formula ` (text( A1:A,"m/dd/yyyy"))` ---
**Author:** ![Dolapo\_Amusan](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/dolapo_amusan/32/16764_2.png) [@Dolapo\_Amusan](https://community.glideapps.com/u/Dolapo_Amusan)\ **Post date:** [August 22, 2021, 9:16pm UTC](https://community.glideapps.com/t/formatting-array-date-time-from-google-sheets/30800/5 "2021-08-22T21:16:01Z")
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. ![Screenshot 2021-08-22 at 22.15.40](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/0/6/06a9529c73d7eb4b4a34e0741a18064c6cde6689.png) ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [August 22, 2021, 9:18pm UTC](https://community.glideapps.com/t/formatting-array-date-time-from-google-sheets/30800/6 "2021-08-22T21:18:14Z")
1. use text formula for that 2. delete last “” from your formula, just have `),))}` ---
**Author:** ![Dolapo\_Amusan](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/dolapo_amusan/32/16764_2.png) [@Dolapo\_Amusan](https://community.glideapps.com/u/Dolapo_Amusan)\ **Post date:** [August 22, 2021, 9:22pm UTC](https://community.glideapps.com/t/formatting-array-date-time-from-google-sheets/30800/7 "2021-08-22T21:22:33Z")
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. ![Screenshot 2021-08-22 at 22.21.11](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/e/9/e99ddb9df13b51a59b0760be0c9ec0f067e31187.png) ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [August 22, 2021, 9:24pm UTC](https://community.glideapps.com/t/formatting-array-date-time-from-google-sheets/30800/8 "2021-08-22T21:24:40Z")
`ARRAYFORMULA(IF(I2:I<>"",(text( I2:I,"m/dd/yyyy")),))}` ---
**Author:** ![Dolapo\_Amusan](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/dolapo_amusan/32/16764_2.png) [@Dolapo\_Amusan](https://community.glideapps.com/u/Dolapo_Amusan)\ **Post date:** [August 22, 2021, 9:34pm UTC](https://community.glideapps.com/t/formatting-array-date-time-from-google-sheets/30800/9 "2021-08-22T21:34:07Z")
> [@Uzo](#): > > ARRAYFORMULA(IF(I2:I\<\>“”,(text( I2:I,“m/dd/yyyy”)),))} Thanks, this worked. Really appreciate you!!! ---
**Author:** ![NoCodeAndy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nocodeandy/32/62530_2.png) [@NoCodeAndy](https://community.glideapps.com/u/NoCodeAndy)\ **Post date:** [January 17, 2024, 4:50pm UTC](https://community.glideapps.com/t/formatting-array-date-time-from-google-sheets/30800/10 "2024-01-17T16:50:50Z")