Text to Date conversion

[fahadozair]

Hi,

I have a scenario say I am receiving a file say at date time "2022-12-15 11.02.29.965", I start processing the file at date time "2022-12-15 11.02.36.104" and it takes time "00 00:00:00.105" to process the file. 

I am getting several records in such format in excel. My requirement is that I want to sum up and subtract date time in excel.

"2022-12-15 11.02.36.104" + "00 00:00:00.105" - "2022-12-15 11.02.29.965"

Excel is not considering it as Date Time, and I am unable to perform Addition and Subtraction on it. I tried several ways to convert data in to Date time format, but it is considering it as a text.

Could someone please suggest how can I do this.

[#2]

@fahadozair 

Let's say you have such a value in A2.

Try the following formula in another cell to convert it:

=1*SUBSTITUTE(SUBSTITUTE(A2,".",":",1),".",":",1)

If it works, the result will look like a number. Format the cell with the formula as date and time.

[#3 · Accepted solution]

@HansVogelaar 

Thanks a lot Hans for your Help and Support. It is working.

Could you please also let me know how to convert "00 00:00:00.105" to time format so that I can add it to "2022-12-15 11.02.36.104" so that output is something like  "2022-12-15 11.02.36.209"

[#4]

@fahadozair 

With a time string in A3, use

=1*MID(A3,4,12)

You can add this to the converted date/time.

[#5]

Once again Thanks a lot Hans for your help and support. Greatly Appreciated.