time minus time

If data was entered that way, and depending on your regional settings, Excel doesn't recognize it as a date, so it's just text.

(If it were entered as a date and time, it could FORMATTED as yyyy.mm.dd hh:mm to appear that way.)

If you have to do this routinely, I'd suggest a VBA solution.

If it's a one-off, I'd do it the quickest way I can think of without thinking much:

• Do Data > Text to columns, Delimited, use Space as a delimiter to separate the date and time (need a blank column to right to receive the time)
  
• Select the first column again, Data > Text to columns, Next, Next, select Date and DMY from dropdown, Finish (no extra column needed; works in situ)
  
• Then add those two columns together, copy and replace with values, delete the now-unneeded columns, and proceed on course.

It takes much longer to read this than to do it.

Or if amending originals is not viable you might perhaps get away with:

C1: =SUBSTITUTE(B1,".","-")-SUBSTITUTE(A1,".","-")
C1 format as [mm]:ss
copied down

Assuming you have dates in A1 and B1 then yes you would just put the formula into C1 otherwise you will need to adjust the cell references. The "" around something designate it as a text string.

Dom

Hi,

DonkeyOte is proposing the use of the function Substitute() ... since your cells are formatted as text and are not formatted as dates ...

see attached

HTH

No need to repeat your post - see JR's prior attachement which illustrates the concept.

Bsaed on your prior posts the string format of: yyyy.mm.dd hh:mm is not valid for coercion - you need to replace the period delimiters with a hyphen ie yyyy-mm-dd hh:mm at which point the string can be coerced to true datetime value (date.time).
The subtraction of one string from the other coerces both strings simultaneously.

Not a problem - is your issue resolved or do you still require assistance ?