Google Sheets can't understand timestamp format containing timezone information

Question

GitHub API returns timestamps like so 2018-10-03T10:30:51.444Z which as a programmer is not unusual. But when I put this into a Google sheet cell, it refuses all my attempts to get it recognised as a valid date so I can change the display format (e.g. have it shown in user's local time in a more human-friendly format).

Google is normally good at auto-detecting dates and I've no idea how to force it to - how can I do this ideally without having to manipulate the original string or using multiple cells?

Answer 1

Google Sheets date handling doesn't include a way to directly recognize date-time that include time zones, but JavaScript does it.

You could create a script to return a value that Google Sheets could recognize as a date. I.E. you could create a custom function that use a cell with a date-time string and returns a date object.

Related

• Timezone conversion in a Google spreadsheet

Answer 2

I had a similar challenge and I was able to create a "catch all" for potential date fields that may be returned in any of the po ssible endpoints selected the format that Github returns is very similar to JSON if not for a string designation to the element value. Ill post my code for you to reference and for others to comment on as i'm new as well

***** Replace dataRow.[this value] to the fild name returned that you are working with *******

function yourFunction(){
var response = UrlFetchApp.fetch(apiUrl,options);
var dataAll;     
var obj = JSON.parse(response.getContentText());
      var objData =  Object.getOwnPropertyDescriptor(obj,choice);
      var i;
      for ( i = 0 ; i < objData.value.length; i++ ){
        var dataRow = objData.value[i];
          if(dataRow.orderedDate){
            var orDate = new Date(dataRow.orderedDate).toJSON();
            dataRow.orderedDate = new Date(orDate).toUTCString();
            var objDataGood = dataRow.orderedDate;
            Logger.log("orderedDate: ", objDataGood);
          }
          if(dataRow.receivedDate){
            var reDate = new Date(dataRow.receivedDate).toJSON();
            dataRow.receivedDate = new Date(reDate).toUTCString();
            Logger.log("receivedDate: ", dataRow.receivedDate);
          }
          if(dataRow.timeStamp){
            var tsDate = new Date(dataRow.timeStamp).toJSON() ;
            dataRow.timeStamp = new Date(tsDate).toUTCString();
            Logger.log("timeStamp: ", dataRow.timeStamp);
          }
          if(dataRow.createTime){
            var crDate = new Date(dataRow.createTime).toJSON() ;
            dataRow.createTime = new Date(crDate).toUTCString();
            Logger.log("createTime: ", dataRow.createTime);
          }
          if(dataRow.dob){
            var dbDate = new Date(dataRow.dob).toJSON() ;
            dataRow.dob = new Date(dbDate).toUTCString();
            Logger.log("Date of Birth: ", dataRow.dob);
            }
          if(dataRow.arrivalDate){
            var arDate = new Date(dataRow.arrivalDate).toJSON() ;
            dataRow.arrivalDate = new Date(arDate).toUTCString();
            Logger.log("arrivalDate: ", dataRow.arrivalDate);
            }
         dataAll.push(dataRow); // <- recursive call
         }
      return dataAll;
      }

Hope this helps more than hinders