Difference between two dates in hours

[smide]

Hello.


In columns B and C (B2:B600, C2:C600) I'm receiving dates (with hours and minutes) in rather strange form - without current year?


For example in cell B2: 12.02. 19:07


Explanation: 12 - day, 02 - month (February), then after 02. there is a one space, 19 - hours, 07 - minutes


In cell C2: 14.02. 11:05


I need to calculate difference between dates C2-B2 in hours.


Note: all those dates have the same year 2018

Example:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]12.02. 19:07[/TD]
[TD="align: center"]14.02. 11:05[/TD]
[TD="align: center"] C2-B2 (in hours)[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]....[/TD]
[TD="align: center"]....[/TD]
[TD="align: center"]....[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]....[/TD]
[TD="align: center"]....[/TD]
[TD="align: center"]....[/TD]
[/TR]
</tbody>[/TABLE]

​

 

[Marcílio_Lobão]

smide, Good afernoon.

Are your data a TEXT or a REAL DATE with a strange Cell format?

 

[smide]

Text

 

[cerfani]

you should just parse the text and create dates, then you can add/subtract them... the result will be in days and you can multiply by 24 to get hours....

you need to use functions like MID SEARCH LEFT RIGHT to parse text and then use the date function to create a date with the parsed values

https://www.techonthenet.com/excel/formulas/date.php

 

[63falcondude]

Try this in D2:

=(DATE(2018,MID(C2,4,2),LEFT(C2,2))+RIGHT(C2,5)-(DATE(2018,MID(B2,4,2),LEFT(B2,2))+RIGHT(B2,5)))*24

or

=DATE(2018,MID(C5,4,2),LEFT(C5,2))+RIGHT(C5,5)-(DATE(2018,MID(B5,4,2),LEFT(B5,2))+RIGHT(B5,5)) formatted as Custom [h]:mm