re: Need excel help - automate text to date time format

This is a perfect use case for asking chatGPT how to do something. I use it for crafting advanced Excel formulas all the time.

From ChatGPT

quote:

---

In Excel, I have a dataset with a column that displays date time in text in the following format:

Thu Jun 1 2023 09:49:47 GMT-0500 (central Daylight time)

The goal is to have another column that will calculate NOW minus the above colum, with the result in days. And more importantly I want to have that automated.

ChatGPT Response
To calculate the difference between the current date and the dates in your dataset in Excel, you can use the following steps:

Assuming your dataset starts from cell A2, enter the following formula in cell B2:

excel
Copy code
=DATEDIF(DATEVALUE(MID(A2,FIND(" ",A2)+1,3)&" "&MID(A2,FIND(" ",A2)+5,1)&" "&MID(A2,FIND(" ",A2)+6,4)),TODAY(),"d")
Drag the formula down to apply it to the remaining cells in column B.

This formula works by extracting the relevant parts from the date text using the MID and FIND functions. It then converts the extracted text into a valid date format using DATEVALUE. Finally, it calculates the difference between the converted date and the current date using the DATEDIF function, specifying "d" as the unit to get the result in days.

As you requested automation, the formula uses the TODAY() function, which will always give you the current date. Therefore, whenever you open the Excel file or recalculate the sheet, the formula will update automatically to reflect the current date.

---