Convert text information to excel date format

[beca]

Hi all, I have a spreadsheet generated from a software with date information. This date information is a text, not in excel date format.

I would like to convert this text to excel date format dd/mm/yyyy.

The spreadsheet looks like below.

Thank you all.

 

[Bosquedeguate]

In an adjacent column you can enter:
=DATEVALUE(E7)
for example. This will convert the date string to a date value. The you can set these values to your desired date format.
If you have many dates to convert you may want to use a VBA macro to do the conversions.
Once converted you can copy the dates back to the original cells (column).

How many rows of data need this conversion?

Is this something you need to do regularly or just once?

 

[Canoer]

Use this formula in an adjacent column: =--TEXT(C1,"MM/DD/YYYY") where C1 is the cell with the date as a text.
(Use a double minus sign after the equals and before the word TEXT.) Format the adjacent column as a date.

 

[beca]

In an adjacent column you can enter:
=DATEVALUE(E7)
for example. This will convert the date string to a date value. The you can set these values to your desired date format.
If you have many dates to convert you may want to use a VBA macro to do the conversions.
Once converted you can copy the dates back to the original cells (column).

How many rows of data need this conversion?

Is this something you need to do regularly or just once?

Hi, the formula doesn't work. I have uploaded the spreadsheet, would you please check from your end.

Spreadsheet.xlsx
	A
1	Transaction Apply Date
2	May 12, 2023
3	May 12, 2023
4	May 12, 2023
5	Jun 5, 2023
6	Jul 7, 2023
7	Jul 7, 2023
8	Jul 7, 2023
9	Jul 14, 2023
10	Apr 7, 2023
11	Apr 25, 2023
12	Apr 25, 2023
13	Apr 14, 2023
14	Apr 14, 2023
15	Apr 14, 2023
16	Apr 10, 2023
17	Sep 26, 2022
18	Oct 24, 2022
19	Oct 13, 2022
20	Oct 12, 2022
21	Nov 11, 2022
22	Nov 1, 2022
23	Nov 1, 2022
24	Jun 6, 2022
25	Jun 5, 2023
26	Jun 26, 2023
_Report Output_TimeDetails_Nico

 

[beca]

Use this formula in an adjacent column: =--TEXT(C1,"MM/DD/YYYY") where C1 is the cell with the date as a text.
(Use a double minus sign after the equals and before the word TEXT.) Format the adjacent column as a date.

Hi, the formula doesn't work. I have uploaded the spreadsheet, would you please check from your end.

Spreadsheet.xlsx
	A
1	Transaction Apply Date
2	May 12, 2023
3	May 12, 2023
4	May 12, 2023
5	Jun 5, 2023
6	Jul 7, 2023
7	Jul 7, 2023
8	Jul 7, 2023
9	Jul 14, 2023
10	Apr 7, 2023
11	Apr 25, 2023
12	Apr 25, 2023
13	Apr 14, 2023
14	Apr 14, 2023
15	Apr 14, 2023
16	Apr 10, 2023
17	Sep 26, 2022
18	Oct 24, 2022
19	Oct 13, 2022
20	Oct 12, 2022
21	Nov 11, 2022
22	Nov 1, 2022
23	Nov 1, 2022
24	Jun 6, 2022
25	Jun 5, 2023
26	Jun 26, 2023
_Report Output_TimeDetails_Nico

 

[Rick Rothstein]

Select all of your "dates", call up the Text To Columns dialog box (Data tab, Data Tools panel) and click the Finish button as soon as the dialog box appears. Your value should now all be real dates which you can now format with whatever date format you want.

 

[Dave Patton]

Select the dates such as A2:A26
Use Data TextToColumns
In the last option screen
Date: select MDY
Click Finish
Format the cells to your preference

 

[Rick Rothstein]

Select the dates such as A2:A26
Use Data TextToColumns
In the last option screen
Date: select MDY
Click Finish
Format the cells to your preference

I don't think you have to go past the first dialog page (see my reply to the OP above). Unless it only worked because of my locale (US), when I called up the Text To Column dialog box and clicked the Finish button as soon as the dialog box appeared, it correctly converted all the text dates to real (serial) dates.

 

[Dave Patton]

I added the mention of Date MDY since the original post stated "I would like to convert this text to excel date format dd/mm/yyyy"

N.B. My system has regional settings of dd-mm-yy. (not USA format)
If the OP's system has similar regional settings, he will have to specify the source data's format "MDY"

 

[Rick Rothstein]

I added the mention of Date MDY since the original post stated "I would like to convert this text to excel date format dd/mm/yyyy"

N.B. My system has regional settings of dd-mm-yy. (not USA format)
If the OP's system has similar regional settings, he will have to specify the source data's format "MDY"

Hmm! That is interesting and unexpected... I would have expected the month, as a word, would have made that determination for Text To Columns.