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!

CodePudding user response：

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

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

• 

  However, to solve that 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.

  1- check the timezone of your spreadsheet

  2- AND the timezone of your script editor.

  To do that in script editor, check the parameters

  • click on the gear in the left hand side) and check the third parameter
  • go back to the scripts
  • modifie time zone in appscript.json and make sure it is the same as your spreadsheet (for instance Europe/paris)