In Google Sheets, subtract string "YYYY-MM-DD HH:MM:SS ET" from =now(). How to convert str-CodePudding

We have the string 2022-06-01 11:01:05 ET in Google Sheets and we are looking to compute the difference between this string and =now(). We cannot simply subtract the cells because we cannot subtract a string from a number/date. How can we subtract and get the difference between timestamps (not just dates, precision to the hh:mm:ss would be useful.). The output we are looking for is a simple decimal number representing the time difference, that we can convert into minutes or seconds as needed.

CodePudding user response：

try:

=(NOW()*1)-(1*REGEXEXTRACT(A1, "(.*) "))

then:

=TEXT((NOW()*1)-(1*REGEXEXTRACT(A1, "(.*) ")), "[h]:mm:ss")

CodePudding user response：

See my comment to your original post. But as your post is written, you need to do this for only one cell. So assuming that cell is A2:

=NOW()-REGEXEXTRACT(A2,"(. )\s\S $")

... then set the format of that output to Duration.

Understand that since the NOW function is volatile (constantly changing), the output will also constantly be changing. Hopefully, that is what you want and expect.