How to conditionally change a date based on a time? Time zone differences?

Question

I am using two functions to extract both the date & time out of a string:

Raw String in DATA!A2: 2022-03-06T04:52:33.813Z

=LEFT(DATA!$A$2,FIND("T",DATA!$A$2)-1) to reformat a string into a date:

Result: 2022-03-06

In another cell I am extracting the time and converting it to USA CT time: =MID(DATA!$A$2,12,5)-"6:00"

Result: 10:52 PM

---

The Issue:

I have successfully subtracted 6 hours to convert this time to CT, but now whenever the time is on or after 6 PM CT, the date (2022-03-06) prematurely advances to the next day. In the example above, because 10:52 PM is after 6 PM, the date should be showing 2022-03-05.

Are there any ways to check if the time is after 6 PM, and if that is TRUE, to correct the date by -1 days?

Would sincerely appreciate any help on this.

Thank you!

Answer 1

Since Google Sheets does not recognize the raw string as a date, the best option for this would actually be to create a custom function which converts the string into a date. In this way, it will be easier to perform the date operations you want.

---

To do so, you will have to go to Extensions > Apps Script and use the following code for the custom function from here:

/**
 * Converts a string to a date-time value
 * @customfunction
 */
function DATETIME(dateTimeString) {
  var output = new Date (dateTimeString);
  if(!isNaN(output.valueOf())) {
    return output;
  } else {
    throw new Error('Not a valid date-time');
  }   
}

Save the project, run it, authorize the script and return to the sheet.

Afterwards, in your sheet, call the DATETIME function as you would with an already existing Sheets function and subtract the hours using the TIME function:

=(DATETIME(A2)-TIME(7,0,0))

Assuming your sheet looks like this, the resulting column will look like this:

If you also want to separate the date from the time, you will have to add two more formulae (assuming that D1 contains the result from the DATETIME custom function):

• For retrieving the date:

=LEFT(D1,FIND(" ",D1)-1) 

• For retrieving the time:

=RIGHT(D1,LEN(D1)-FIND(" ",D1))

After all the formulae, this is how the sheet will look like:

Reference

• Datetime with timezone in Google Sheets;

• TIME Sheets Function;

• LEFT Sheets Function;

• RIGHT Sheets Function.