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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

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,126
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,514
Messages
5,548,500
Members
410,841
Latest member
rcp
Top