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

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?

A-

Try, without any script

=value(left(A1,10))+value(mid(A1,12,12))

B-

If you want to subtract 6 hours, try

=value(left(A1,10))+value(mid(A1,12,12))-6/24

you will change the date if any

C-

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)