google-sheetsgoogle-sheets-formula

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?

Solution

  • A-

    Try, without any script

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

    enter image description here

    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

    enter image description here