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

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

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