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

 

[WESTERNWALL]

Hi Etaf
You didn't think you would be hearing from me again. I received another file with date format issues. I tried your solution and it does not work. I tried delving into the formula and make changes, but that did not work either. Could you help me on this one, please? The date format should be dd/mm/yyyy. Many thanks.

MrExcel Example Date format Issue #2.xlsx
	A	B
1	10/15/2023 04:42:26	#VALUE!
2	10/15/2023	45214.19613
3	10/15/2023 04:50:55
4	10/15/2023 05:01:22
5	10/15/2023 05:04:12
6	10/15/2023 05:06:12
7	10/15/2023 05:07:33
8	10/15/2023 05:18:33
9	10/15/2023 05:22:56
10	10/15/2023 05:28:27
11	10/15/2023 05:32:05
12	10/15/2023 05:32:27
13	10/15/2023 05:38:18
Sheet1

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

 

[WESTERNWALL]

Hi Etaf
You didn't think you would be hearing from me again. I received another file with date format issues. I tried your solution and it does not work. I tried delving into the formula and make changes, but that did not work either. Could you help me on this one, please? The date format should be dd/mm/yyyy. Many thanks.

MrExcel Example Date format Issue #2.xlsx
	A	B
1	10/15/2023 04:42:26	#VALUE!
2	10/15/2023	45214.19613
3	10/15/2023 04:50:55
4	10/15/2023 05:01:22
5	10/15/2023 05:04:12
6	10/15/2023 05:06:12
7	10/15/2023 05:07:33
8	10/15/2023 05:18:33
9	10/15/2023 05:22:56
10	10/15/2023 05:28:27
11	10/15/2023 05:32:05
12	10/15/2023 05:32:27
13	10/15/2023 05:38:18
Sheet1

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

 

[WESTERNWALL]

Apologies, Etaf. That was the incorrect file I sent. Please disregard it. Thanks

 

[WESTERNWALL]

Hi Etaf
You didn't think you would be hearing from me again. I received another file with date format issues. I tried your solution and it does not work. I tried delving into the formula and make changes, but that did not work either. Could you help me on this one, please? The date format should be dd/mm/yyyy. Many thanks.

Mr Excel DATEVALUE Formula Issue.xlsx
	A	B
1	1/13/2024	#VALUE!
2	1/13/2024	#VALUE!
3	1/13/2024
4	1/13/2024
5	1/14/2024
6	1/14/2024
7	1/15/2024
8	1/15/2024
9	1/16/2024
10	1/17/2024
11	1/17/2024
Sheet1

Cell Formulas
Range	Formula
B1	B1	=VALUE(A1)
B2	B2	=DATEVALUE(MID(A2,4,2)&"/"&LEFT(A2,2)&"/"&MID(A2,7,4))

 

[Alex Blakenburg]

You seem to be overcomplicating things by getting hung up on the fact that you have partially converted from the original format of 2024-01-13T00:22:30Z.
The original format is a much simpler conversion than the one you are dealing with now.
Additional issues:
• If you have Excel 2021 or MS 365 there are other options available to you, so please update your account profile to show which version you are using
• Typically Excel will be treating any dates with days <= 12 differently and will have converted them to a date data type but with month and year the wrong way around but you have not included any of those in your sample data.

20240513 VBA Convert UTC Date yyyymmddThhmmssZ WESTERNWALL.xlsm
	A	B	C					H	I	J
1	1/13/2024	13/01/2024							2024-01-13T00:22:30Z	13/01/2024
2	1/13/2024	13/01/2024							2024-01-13T00:29:45Z	13/01/2024
3	1/13/2024	13/01/2024							2024-01-13T00:46:03Z	13/01/2024
4	1/13/2024	13/01/2024							2024-01-13T01:14:41Z	13/01/2024
5	1/14/2024	14/01/2024							2024-01-14T01:33:40Z	14/01/2024
6	1/14/2024	14/01/2024							2024-01-14T01:37:48Z	14/01/2024
7	1/15/2024	15/01/2024							2024-01-15T01:37:54Z	15/01/2024
8	1/15/2024	15/01/2024							2024-01-15T02:11:08Z	15/01/2024
9	1/16/2024	16/01/2024							2024-01-16T02:40:29Z	16/01/2024
10	1/17/2024	17/01/2024							2024-01-17T02:43:21Z	17/01/2024
11	1/17/2024	17/01/2024							2024-01-17T02:44:52Z	17/01/2024
12	1/10/2024	10/01/2024							2024-01-10T02:44:52Z	10/01/2024
13
14	Partially Converted data	Convert Column A							Original Data	Convert Column I
Latest

Cell Formulas
Range	Formula
B1:B12	B1	=IF(ISNUMBER(A1), DATEVALUE(TEXT(A1,"mm/dd/yyyy")), DATE(RIGHT(A1,4),LEFT(A1,FIND("/",A1)-1),SUBSTITUTE(MID(A1,3,3),"/","")))
J1:J12	J1	=DATEVALUE(LEFT(I1,10))