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.
In Google Sheets, subtract string "YYYY-MM-DD HH:MM:SS ET" from =now(). How to convert string to number?
Viewed 213
3 Answers
try:
=(NOW()*1)-(1*REGEXEXTRACT(A1, "(.*) "))
then:
=TEXT((NOW()*1)-(1*REGEXEXTRACT(A1, "(.*) ")), "[h]:mm:ss")
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.
You mention:
We have the string
2022-06-01 11:01:05 ETin Google Sheets...
It is safer to use:
=NOW()-REGEXEXTRACT(B1, "(.*) ")+(x/24)
(where x=difference in time zones and B1 is the cell containing the sting)
As an example, if your sheet's time zone is "(GMT+00:00) London", x should be adjusted accordingly
WHY?
Both of the answers provided by Erik as well as player0 will work if and only IF you are in the same time zone.
Considering the fact though that you do mention that the time zone in your question is a string (meaning fixed) as well as it is quite common for a Google Sheet to be in a different time zone, it is safer to use the above formula.
Please have a look at these times
Related