Compare time only when time zone changed in 1920

Question

I have a time value of "7:00:00 AM" in my spreadsheet. Hypothetically, I want to compare this to another hour/minute time value (date agnostic) in my Google Apps Script code. When the script reads the value, because no date was attached, it assumes a date of 30-Dec-1899. Now, the issue is, because the time zone where I currently am was GMT+06:42:04 back in 1899, the time value appears as "07:17:56".

How can I compare this time with another to know if the hours and minutes match? This would ideally be done with vanilla JS (i.e. I prefer not to use Moment.js).

function getCellValue() {
  var cellValue = SpreadsheetApp.getActiveSheet().getRange("A1").getValue();
  Logger.log(cellValue); // Sat Dec 30 07:00:00 GMT+06:42 1899
  Logger.log(cellValue.getHours()); // 7.0
  Logger.log(cellValue.getMinutes()); // 17.0
  Logger.log("Untouched: " + cellValue); // Untouched: Sat Dec 30 1899 07:17:56 GMT+0700 (ICT)
  var formattedTime = Utilities.formatDate(cellValue, "GMT+7", "EEE MMM dd yyyy HH:mm z");
  Logger.log("Formatted: " + formattedTime); // Formatted: Sat Dec 30 1899 07:17 GMT+07:00
  var updatedCellValue = new Date(cellValue.setFullYear(2018));
  Logger.log("New Year: " + updatedCellValue); // New Year: Sun Dec 30 2018 07:17:56 GMT+0700 (ICT)
  var updatedDate = new Date(cellValue);
  Logger.log("Converted: " + updatedDate); // Converted: Sat Dec 30 1899 07:17:56 GMT+0700 (ICT)
  Logger.log("Converted & formatted: " + Utilities.formatDate(updatedDate, "GMT+7", "EEE MMM dd yyyy HH:mm z")); // Converted & formatted: Sat Dec 30 1899 07:17 GMT+07:00
}

Both my spreadsheet and script are set to "(GMT+07:00) Bangkok" time zone.

(I understand that 7am today and 7am in 1899 were not the same, but that is irrelevant to this hypothetical. If a user enters 7am in the spreadsheet, they probably mean that to be 7am this century.)

Seems like a great time for getDisplayValue() and then constructing the date in apps script from the "hh:mm am/pm" string — tehhowch
It's a problem involving JavaScript, dates, and time zones. Expecting elegance is being far too generous :D — tehhowch
lol, your thought inspired my solution @tehhowch, which is fairly simple. Thanks. — Diego

Diego Diego · Accepted Answer · 2018-04-11T17:47:38

getDisplayValue(), not getValue()
Create a new Date object from value
Use Utilities.formatDate() to get the hour & minutes

Fixed code:

function getCellValue() {
  var cellValue = SpreadsheetApp.getActiveSheet().getRange("A1").getDisplayValue();
  Logger.log(cellValue); // 7:00:00 AM (or 0.29, if formatted as number)
  cellValue = new Date(cellValue);
  var hours = Utilities.formatDate(cellValue, "GMT+7", "HH");
  var minutes = Utilities.formatDate(cellValue, "GMT+7", "mm");
  Logger.log (hours + ":" + minutes); // 07:00
}

Compare time only when time zone changed in 1920

1 Answers