SOLVED

Text to Date conversion

Copper Contributor

Text to Date conversion

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.

4 Replies

Re: Text to Date conversion

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.

best response confirmed by fahadozair (Copper Contributor)
Solution

Re: Text to Date conversion

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"

Re: Text to Date conversion

With a time string in A3, use

=1*MID(A3,4,12)

You can add this to the converted date/time.

Re: Text to Date conversion

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

Accepted Solutions
best response confirmed by fahadozair (Copper Contributor)
Solution

Re: Text to Date conversion

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"