- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Need excel help - automate text to date time format
Posted on 6/1/23 at 10:00 am
Posted on 6/1/23 at 10:00 am
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.
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
Posted on 6/1/23 at 11:27 am to castorinho
have you tried DATEFORMAT?
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.
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.
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.
Posted on 6/1/23 at 1:56 pm to mdomingue
OK let me try that
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")
=DATEDIF(DATEVALUE(INDEX(TEXTSPLIT(K1," "),2) & " " & INDEX(TEXTSPLIT(K1," "),3) & ", " & INDEX(TEXTSPLIT(K1," "),4)) +TIMEVALUE(INDEX(TEXTSPLIT(K1," "),5)),NOW(),"yd")
Posted on 6/1/23 at 2:25 pm to castorinho
Used LEFT(A1, 10)&","RIGHT(LEFT(S2) - 33), 14) To insert the comma. Then I applied your formula to that worked perfectly!
Thank you.
Thank you.
Posted on 6/1/23 at 2:32 pm to castorinho
Greet, glad to help
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
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.
This post was edited on 6/1/23 at 2:47 pm
Posted on 6/1/23 at 2:52 pm to Chromdome35
Asking it for a second option:
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.
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News