Google Sheets - Date Format Display Issues

user_9816773 0
Viewed 339

I'm currently building dashboards in Google Sheets and have run into a consistent issue with dates being displayed as a number opposed to a date format. EX: 43626 opposed to 6/11/19.

This data is being pulled from form sheet into a database, then queried into the dashboard spreadsheet where it is coming through as a number sequence opposed to readable date format. Currently have tried to use the DATEVALUE to reformat, as well as attempted to reformat the cell into a date format in both dashboard and database locations, with no luck.

//What I've tried

=TargetCell

43626

=DATEVALUE(TargetCell)

43626

=TargetCell //Reformatted as any Date format

43626

I was expecting the following

=DATEVALUE(TargetCell)

6/11/19

OR:

=TargetCell //Reformatted as any Date format

6/11/19

google-sheets-query google-sheets-formula google-sheets formatting date
1 Answers

you got it wrong... function DATEVALUE creates 43626 from 6/11/2019

if you want to reverse it you need to use either:

  • internal custom date formatting (does not work all the times)
  • TEXT formula like: =TEXT(43626, "m/d/yy")
  • QUERY parameter format like: =QUERY(A:B, "format A 'm/d/yy'", 0)
  • or QUERY parameter toDate like: =QUERY(A:B, "toDate(A)", 0)

https://developers.google.com/chart/interactive/docs/querylanguage#top_of_page

user_56326290
Related