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

ExcelBeginner34

New Member
Joined
Mar 2, 2019
Messages
41
Office Version
  1. 2016
Platform
  1. Windows
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!

1603195128918.png


Any help would be very much appreciated.

Best regards

Iain
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,051
Office Version
  1. 365
Platform
  1. MacOS
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

Well-known Member
Joined
Oct 24, 2015
Messages
7,132
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

New Member
Joined
Mar 2, 2019
Messages
41
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 24, 2012
Messages
4,051
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,365
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
ABC
1StartEndDifference
201.02.2020 00:0801.02.2020 00:1800:10:00
301.02.2020 00:0802.02.2020 00:1824:10:00
2b
Cell Formulas
RangeFormula
C2:C3C2=SUBSTITUTE(B2,".","-")-SUBSTITUTE(A2,".","-")
 

Watch MrExcel Video

Forum statistics

Threads
1,114,660
Messages
5,549,278
Members
410,907
Latest member
Gan_77
Top