Search code examples
regexdatedatetimegoogle-sheetsduration

How to find the difference in hours between two dates dd/mm/yyyy hh:mm

I have two dates in cells

A1=05.11.2021 18:16
B1=05.11.2021 20:16

I need to find difference in hours between two dates. Result should be (B1-A1)=2 I can't find an answer on the Internet, I ask for help.

Solution

  • use:

    =TEXT((DATE(
 REGEXEXTRACT(B1, "\d{4}"),
 REGEXEXTRACT(B1, "\.(\d+)\."),
 REGEXEXTRACT(B1, "^\d+"))+INDEX(SPLIT(B1, " "),,2))-(DATE(
 REGEXEXTRACT(A1, "\d{4}"), 
 REGEXEXTRACT(A1, "\.(\d+)\."), 
 REGEXEXTRACT(A1, "^\d+"))+INDEX(SPLIT(A1, " "),,2)), "[h]")

    enter image description here

    arrayformula:

    =INDEX(IFNA(TEXT((DATE(
 REGEXEXTRACT(B1:B, "\d{4}"),
 REGEXEXTRACT(B1:B, "\.(\d+)\."),
 REGEXEXTRACT(B1:B, "^\d+"))+INDEX(SPLIT(B1:B, " "),,2))-(DATE(
 REGEXEXTRACT(A1:A, "\d{4}"), 
 REGEXEXTRACT(A1:A, "\.(\d+)\."), 
 REGEXEXTRACT(A1:A, "^\d+"))+INDEX(SPLIT(A1:A, " "),,2)), "[h]")))

    shorter:

    =INDEX(IFERROR(1/(1/(TEXT(
 REGEXREPLACE(B1:B, "(\d+).(\d+).(\d{4})", "$2/$1/$3")-
 REGEXREPLACE(A1:A, "(\d+).(\d+).(\d{4})", "$2/$1/$3"), "[h]")))))

    enter image description here