Forum Discussion
Solved
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.
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"
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.
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"
With a time string in A3, use
=1*MID(A3,4,12)
You can add this to the converted date/time.
