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.

I can trim the original text to look like a date with LEFT and RIGHT, but having issues with excel picking up that date formatting.

5 ...

Report Post

Posted by whiskey over ice

Member since Sep 2020

3289 posts

Posted on 6/1/23 at 11:27 am to castorinho

have you tried DATEFORMAT?

0 ...

Report Post

Posted by VADawg

Wherever

Member since Nov 2011

45113 posts

Posted on 6/1/23 at 12:14 pm to castorinho

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

Let's assume this is in cell A1. Type =NOW()-A1 in whatever cell you want the result to appear in. Format that cell to general.

If I understand you correctly, this should get you the answer you're looking for.

0 ...

Report Post

Posted by mdomingue

Lafayette, LA

Member since Nov 2010

30853 posts

Posted on 6/1/23 at 12:58 pm to castorinho

There is an issue with your format for the dataset. You need a comma.

Thu Jun 1, 2023 09:49:47 for instance

Then you can use this to get what you want

=DAYS(NOW(),RIGHT(LEFT(A1,FIND(":",A1)-4),LEN(LEFT(A1,FIND(":",A1)-4))-FIND(" ",LEFT(A1,FIND(":",A1)-4))))

If you cannot easily add the comma, I can get add something to do that as well.

1 ...

Report Post

Posted by Brisketeer

Texas

Member since Aug 2013

1442 posts

Posted on 6/1/23 at 2:17 pm to castorinho

This is assuming K1 has the date/time string.

=DATEDIF(DATEVALUE(INDEX(TEXTSPLIT(K1," "),2) & " " & INDEX(TEXTSPLIT(K1," "),3) & ", " & INDEX(TEXTSPLIT(K1," "),4)) +TIMEVALUE(INDEX(TEXTSPLIT(K1," "),5)),NOW(),"yd")

0 ...

Report Post

Posted by Chromdome35

NW Arkansas

Member since Nov 2010

6876 posts

Posted on 6/1/23 at 2:46 pm to castorinho

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.