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.

CodePudding user response：

EDIT:

As what @basic mentioned in the above comment, you can format the cell where your output goes or use text with h for hour difference and [h] for the whole duration in hours (got from Cooper's answer). See usage and difference below:

Text:

=text(B1-A1, "h") 

or

=text(B1-A1, "[h]") 

Update:

• Make sure your Date Times uses proper delimiters. / and - are acceptable (e.g. 5/11/2021 18:16:00 or 5-11-2021 18:16:00). (This depends entirely on your locale.)

• If you want to show it having . as delimiter, just use a custom Date Time format and use . as its delimiter.

• Using custom format:

  • 

• Actual value vs Display value:

  • 

• If you don't want to do any changes to the date time and want to have it as text, then replace them using regexreplace before using them in text.

RegexReplace:

=text(REGEXREPLACE(B1, "\.", "/") - REGEXREPLACE(A1, "\.", "/"), "h")

or

=text(REGEXREPLACE(B1, "\.", "/") - REGEXREPLACE(A1, "\.", "/"), "[h]")

CodePudding user response：

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]")

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]")))))