I created a shared shipment tracker with google sheets where updates are made in a master tab, then filtered into several regional tabs via query function to better visualize shipment status by region. Query used:

=IFERROR(Arrayformula(QUERY(Filter!A2:P&"","select * WHERE Col1 = 'BA'",0)),"No Data Yet")

I made one column (column P) a time stamp column that auto-populates the current date via a simple script every time an update is made in any given cell of the master tab.The script does its job formatting the date as desired, this is the section of the script where I add the number format:

setValue(new Date()).setNumberFormat("mm/dd/yyyy hh:mm:ss");

The problem is that when this data is pulled into any regional tab by the query function, the date format turns into a serial number. I've tried messing with the column formatting in all tabs, however the only way the regional tabs will show the date in my desired format (mm/dd/yyyy hh:mm:ss) is when I manually format the date values in the master tab as 'Plain Text'. Is there a way around this to display the desired date format in both the master and regional tabs without having to continually format the timestamp column in the master tab as plain text every time an update is made?

  • Well, turns out weirdly that Query language only accepts date in one format which is YYYY-MM-DD in text format, but you need to specify the date clause in the query syntax. ___ Ceebee86
    – pjmg
    Commented Dec 26, 2019 at 18:03


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.