Solved: Date/Time Appearing strangely on spreadsheet

MTurner1 · 07-13-2020 03:01 PM

Hi,

So I want to show date on my spreadsheet but its appearing strangely- can anyone advise why this is?
I’ve attached a screenshot of the issue. Any help would be great! (also for the appsheet it should just be date, not date/time for the column!
Screenshot date issue|645x500 )

Mari

MultiTech

I’ve had things like this happen to me. Long story short, there’s some meta-data or something inside the column’s data that’s getting in the way.

Try this… and it sucks, but it works:

Insert a new temporary column
Inside this column, use the formula:
=(column)
- where “column” is a cell reference to the one with the serial number date.
- So, for example, if my dates are in column D, and I added a new column E, my formula would be:
  =(D2)
Copy this formula all the way down the sheet for each row you have data.
set the type of your new column to date
- this should give you your dates as seen as dates.
Copy the entire new column you made, and paste it OVER the original column - replacing it’s data.
- So, continuing our example from before, after dragging the formula down all the rows
- I copy the entire column E
- and paste is over the entire column D.
Then delete your temporary column.

This should result with the same data, but in the appropriate format for your sheet.

View solution in original post

LeventK

@MTurner
What you are seeing is a date serial which means that your Date/Time column is not properly formatted in your spreadsheet. Format your Date/Time column as per Date.

MTurner1

Thanks for your help.

I changed my spreadsheet column to ‘Date’ and on Appsheet gave it the formula TODAY().
Still is showing the serial number!

MultiTech

I’ve had things like this happen to me. Long story short, there’s some meta-data or something inside the column’s data that’s getting in the way.

Try this… and it sucks, but it works:

Insert a new temporary column
Inside this column, use the formula:
=(column)
- where “column” is a cell reference to the one with the serial number date.
- So, for example, if my dates are in column D, and I added a new column E, my formula would be:
  =(D2)
Copy this formula all the way down the sheet for each row you have data.
set the type of your new column to date
- this should give you your dates as seen as dates.
Copy the entire new column you made, and paste it OVER the original column - replacing it’s data.
- So, continuing our example from before, after dragging the formula down all the rows
- I copy the entire column E
- and paste is over the entire column D.
Then delete your temporary column.

This should result with the same data, but in the appropriate format for your sheet.

MTurner1

Thank you! I will try this and let you know.