Converting text to date and time, then calculating the minutes between each

[ExcelBeginner34]

Hi,

Hoping to get some help. A colleague of mine has sent me the attached file. The have asked if there is an easy was to work out the difference in time between col A and B. From what I can tell, the data is just entered as text as it is left aligned. I assume that I need some way to convert this text to a date and time format then subtract the earlier date/time from the latter to work out the difference. I just can't figure out how to do this!

Any help would be very much appreciated.

Best regards

Iain

 

[etaf]

to check if its a date - change the format to general and see if you get a number
if not, i suspect the . maybe causing a text
then click on column a and b to select both columns , then use a FIND/REPLACE
and find a dot . and replace with a /
now it should all turn into dates & times

 

[sandy666]

use Power Query
it should change format of both columns to datetime format the subtract Endzeit / Anfangszeit, I mean select Endzeit then with Ctrl key select Anfangszeit then from the ribbon: Add Column-Standard-Subtract

 

[ExcelBeginner34]

to check if its a date - change the format to general and see if you get a number
if not, i suspect the . maybe causing a text
then click on column a and b to select both columns , then use a FIND/REPLACE
and find a dot . and replace with a /
now it should all turn into dates & times

Thanks for your help, that's worked a treat. Much appreciated.

 

[etaf]

you are welcome

 

[Dave Patton]

What are your regional settings for dates?
What does a normal date look like?
What have your tried?
It would help if you posted your data with Xl2BB.

T202010b.xlsm
	A	B	C
1	Start	End	Difference
2	01.02.2020 00:08	01.02.2020 00:18	00:10:00
3	01.02.2020 00:08	02.02.2020 00:18	24:10:00
2b

Cell Formulas
Range	Formula
C2:C3	C2	=SUBSTITUTE(B2,".","-")-SUBSTITUTE(A2,".","-")