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

quote:

---

Is there a different option to use?

ChatGPT Response

Certainly! An alternative option to calculate the difference in days between the current date and the dates in your dataset is by using the DAYS function in combination with the DATEVALUE and LEFT functions. Here's how you can do it:

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

excel
Copy code
=DAYS(TODAY(), DATEVALUE(LEFT(A2, 15)))
Drag the formula down to apply it to the remaining cells in column B.

In this formula, the LEFT function is used to extract the first 15 characters from the date text, which represent the date in the format "Thu Jun 1 2023". The DATEVALUE function is then used to convert the extracted date text into a valid date format.

The DAYS function calculates the difference in days between the current date (obtained using TODAY()) and the converted date value, giving you the desired result.

Just like the previous solution, this formula will update automatically whenever you open the Excel file or recalculate the sheet.

---