Convert text date to date in excel for Mac

[WESTERNWALL]

Hi
I've got a text date that I want to convert to a numerical date in Excel for Mac. I have tried some of your formulas and they don't seem to work. I can't find VBA to use. I do not have the little green triangle in the corner. Can anyone help please?
Tx

 

[etaf]

how is the text formatted??
what formula have you tried
what version of excel are you using ?

 

[WESTERNWALL]

Hi Etaf
It's formatted as text.
Pretty much all the Googled formulas I can find. Honestly all rubbish.
Excel for Mac Version 16.84

MrExcel Example Date format.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	10/15/2023 04:42:26	#VALUE!							2023-10-15T00:22:30Z
2	10/15/2023 04:43:29								2023-10-15T00:29:45Z
3	10/15/2023 04:50:55								2023-10-15T00:46:03Z
4	10/15/2023 05:01:22								2023-10-15T01:14:41Z
5	10/15/2023 05:04:12								2023-10-15T01:33:40Z
6	10/15/2023 05:06:12								2023-10-15T01:37:48Z
7	10/15/2023 05:07:33								2023-10-15T01:37:54Z
8	10/15/2023 05:18:33								2023-10-15T02:11:08Z
9	10/15/2023 05:22:56								2023-10-15T02:40:29Z
10	10/15/2023 05:28:27								2023-10-15T02:43:21Z
11	10/15/2023 05:32:05								2023-10-15T02:44:52Z
12	10/15/2023 05:32:27								2023-10-15T03:02:20Z
13	10/15/2023 05:38:18								2023-10-15T03:10:37Z
14
15	This is the result from column I								Got rid of the "Z" by Replacing Z in "Find What field".
16	How to procede from here.								Got rid of "T" by replacing T with 1 space in the "Replace With" field
17
18
19
Sheet1

Cell Formulas
Range	Formula
B1	B1	=VALUE(A1)

 

[etaf]

try
=DATEVALUE(LEFT(I1,10))+TIMEVALUE(MID(I1,12,8))

BUT how is 1st Jan formatted
is it 2023-01-01
OR
2023-1-1T00:00:00
in which case would need to add a search for T and Z
in the mix

Also an IFERROR()

Book3
	A	B	C	D	E	F	G	H	I
1	10/15/2023 04:42:26	45214.01563	0.015625						2023-10-15T00:22:30Z
2	10/15/2023 04:43:29	45214.02066							2023-10-15T00:29:45Z
3	10/15/2023 04:50:55	45214.03198							2023-10-15T00:46:03Z
4	10/15/2023 05:01:22	45214.05186							2023-10-15T01:14:41Z
5	10/15/2023 05:04:12	45214.06505							2023-10-15T01:33:40Z
6	10/15/2023 05:06:12	45214.06792							2023-10-15T01:37:48Z
7	10/15/2023 05:07:33	45214.06799							2023-10-15T01:37:54Z
8	10/15/2023 05:18:33	45214.09106							2023-10-15T02:11:08Z
9	10/15/2023 05:22:56	45214.11145							2023-10-15T02:40:29Z
10	10/15/2023 05:28:27	45214.11344							2023-10-15T02:43:21Z
11	10/15/2023 05:32:05	45214.11449							2023-10-15T02:44:52Z
12	10/15/2023 05:32:27	45214.12662							2023-10-15T03:02:20Z
13	10/15/2023 05:38:18	45214.13237							2023-10-15T03:10:37Z
14		#VALUE!
15	This is the result from column I								Got rid of the "Z" by Replacing Z in "Find What field".
16	How to procede from here.								Got rid of "T" by replacing T with 1 space in the "Replace With" field
Sheet1

Cell Formulas
Range	Formula
C1	C1	=TIMEVALUE(MID(I1,12,8))
B1:B14	B1	=DATEVALUE(LEFT(I1,10))+TIMEVALUE(MID(I1,12,8))

 

[WESTERNWALL]

Hi Etaf
Thanks for your time. I was able to convert the formats in column I to the one you see in column A by deleting the letters. The formula that you have suggested to convert the date format in column I does not help to convert the one in column A. I need a formula to convert the date format in column A. I have had this kind of issue before and someone helped me with a macro that worked. I can't find the file I used it in and I can't find the solution in my profile on the messageboard. I'd appreciate a bit more patience. I appreciate your amazing assistance.
Many thanks

 

[etaf]

where are located , and by default do you use DD/MM or MM/DD
as datevalue() may work
it wont for me as i'm UK and use DD/MM
=DATEVALUE(LEFT(A1,10))

for the time
=TIMEVALUE(RIGHT(A1,8))
so can just add that

otherwise if DD/MM

you didnt answer

BUT how is 1st Jan formatted
is it 2023-01-01

=DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&MID(A1,7,4))+TIMEVALUE(RIGHT(A1,8))

Book6
	A	B
1	10/15/2023 04:42:26	45214.19613
2	10/15/2023 04:43:29	45214.19686
3	10/15/2023 04:50:55	45214.20203
4	10/15/2023 05:01:22	45214.20928
5	10/15/2023 05:04:12	45214.21125
6	10/15/2023 05:06:12	45214.21264
7	10/15/2023 05:07:33	45214.21358
8	10/15/2023 05:18:33	45214.22122
9	10/15/2023 05:22:56	45214.22426
10	10/15/2023 05:28:27	45214.22809
11	10/15/2023 05:32:05	45214.23061
12	10/15/2023 05:32:27	45214.23087
13	10/15/2023 05:38:18	45214.23493
Sheet1

Cell Formulas
Range	Formula
B1:B13	B1	=DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&MID(A1,7,4))+TIMEVALUE(RIGHT(A1,8))

 

[WESTERNWALL]

Hi Etaf
I'll first answer your question then I try your suggestion. In the original format it's yyyy-mm-dd hh:mm:ss. When I deleted the letters, using Replace, the format in column converted to mm/dd/yyyy hh:mm:ss. The format I use is dd/mm/yyyy. Before converting the text is there a way to delete the time text on the right, then I only need to contend with converting the date? Or is that not important?
I tried the formula and it does not work.
Many thanks

 

[etaf]

I tried the formula and it does not work.

which formula - confused as we are taking about 2 different formats in column I and Column A

in column I - its formatted as
YYYY-MM-DD
in column A - its formatted as
MM/DD/YYYY

which column are we talking about now - I or A

if you dont want the time , remove the +timevalue

then just use for column A
=DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&MID(A1,7,4))

for column I
=DATEVALUE(LEFT(I1,10))

 

[WESTERNWALL]

Wow, Efat! It worked. many thanks for your time and patience.

 

[etaf]

you are welcome