Date column not working

[scolty1985]

Hi,

Im trying to plot some data but its been downloaded in the wrong chronological order. So i attempted to filter it and it screwed everything up. After looking into the values, so are dates and others are text. I tried using the format cell function but that didnt work. I also tried using the text function



which also didnt work.

date column not working.xlsx
	A	B	C	D	E	F	G	H
1	Date	Day	Price	Open	High	Low	Vol.	Change %
2	04/08/2021	08/04/2021	975.58	965.99	975.58	962.1	654.72M	1.16%
3	04/07/2021	07/04/2021	964.41	963.04	968.66	957.95	538.83M	0.20%
4	04/06/2021	06/04/2021	962.44	958.74	963.57	950.62	625.31M	0.69%
5	04/05/2021	05/04/2021	955.81	963.98	968.12	953.42	433.05M	-0.23%
6	04/01/2021	01/04/2021	957.99	953.17	963.06	948.93	359.68M	0.45%
7	03/31/2021	03/31/2021	953.66	979.3	979.31	943.3	591.55M	-2.76%
8	03/30/2021	03/30/2021	980.75	985.76	988.68	972.99	485.20M	-0.70%
9	03/29/2021	03/29/2021	987.66	976.09	999.17	976.03	486.11M	1.45%
10	03/26/2021	03/26/2021	973.52	973.14	976.7	965.26	382.36M	0.34%
11	03/25/2021	03/25/2021	970.2	967.06	976.4	953.76	582.99M	0.32%
12	03/24/2021	03/24/2021	967.07	977.25	980.52	963.2	374.71M	-1.56%
13	03/23/2021	03/23/2021	982.35	998.79	1,002.60	980.37	480.02M	-1.90%
14	03/22/2021	03/22/2021	1,001.41	1,005.33	1,007.70	996.56	410.76M	-0.45%
15	03/19/2021	03/19/2021	1,005.96	994.58	1,008.22	992.35	546.46M	0.99%
16	03/18/2021	03/18/2021	996.09	983.87	998.25	983.75	399.37M	1.56%
17	03/17/2021	03/17/2021	980.79	979.11	986.5	976.92	418.84M	0.18%
18	03/16/2021	03/16/2021	979.08	981.26	986.53	974.83	419.43M	-0.12%
19	03/15/2021	03/15/2021	980.28	994.49	998.46	980.13	430.23M	-1.25%
20	03/12/2021	12/03/2021	992.71	969.73	993.14	967.73	379.61M	3.09%
21	03/10/2021	10/03/2021	962.99	948.31	966.25	944.15	319.30M	1.90%
22	03/09/2021	09/03/2021	945.02	944.96	956.11	936.38	624.48M	0.56%
23	03/08/2021	08/03/2021	939.78	942.6	955.71	938.09	750.66M	0.31%
24	03/05/2021	05/03/2021	936.86	942.09	948.98	933.89	512.84M	-1.12%
25	03/04/2021	04/03/2021	947.46	963.36	963.85	947.46	424.67M	-1.58%
26	03/03/2021	03/03/2021	962.65	976.11	978.26	960.16	311.78M	-1.09%
27	03/02/2021	02/03/2021	973.26	959.15	975.43	956.7	476.57M	2.22%
28	03/01/2021	01/03/2021	952.11	957.08	958.84	939.9	581.20M	0.11%
29	02/26/2021	02/26/2021	951.1	951.18	957.2	933.57	486.14M	-1.10%
30	02/25/2021	02/25/2021	961.66	958.12	964.7	953.95	565.20M	0.89%
31	02/24/2021	02/24/2021	953.19	980.18	983.48	947.67	577.56M	-2.22%
32	02/23/2021	02/23/2021	974.84	970.88	981.35	968.58	389.66M	0.63%
IDX Basic Industry Historical D

Cell Formulas
Range	Formula
B2:B32	B2	=TEXT(A2,"mm/dd/yyyy")

Any help would be appreciated.

Rgds

Fraz

 

[awoohaw]

Once you do the import use the TEXT TO COLUMNS procedure on your suspect date column. It should only reformat text that look like dates into numbers.
And as long as the date that the text is in is in a generally accepted date format it will convert it for you.
Its on the DATA RIBBON .... DATA>>DATATOOLS >> TEXT TO COLUMNS

if you have excel 365 or better you can use the text before and text after functions to parse out the year month and day into the DATE function if that doesn't work. Or maybe even in POWER QUERY, but I am not an expert on that.


Here is a suggestion using the DATE, TEXTBEFORE, and TEXTAFTER Functions:
Again, for 365 and later, I think. If you don't have the last two functions you'll have to use left, right, and mid to parse it out, like this:

=IF(ISNUMBER(A2),A2,DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2)))

I kept the column format as GENERAL, change to the date format as you desire.

Mr excel questions 58.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Day	Price	Open	High	Low	Vol.	Change %		Col A Conv	Col B Conv
2	44412	08/04/2021	975.58	965.99	975.58	962.1	654.72M	0.0116		44412	44412
3	44381	07/04/2021	964.41	963.04	968.66	957.95	538.83M	0.002		44381	44381
4	44351	06/04/2021	962.44	958.74	963.57	950.62	625.31M	0.0069		44351	44351
5	44320	05/04/2021	955.81	963.98	968.12	953.42	433.05M	-0.0023		44320	44320
6	44200	01/04/2021	957.99	953.17	963.06	948.93	359.68M	0.0045		44200	44200
7	03/31/2021	03/31/2021	953.66	979.3	979.31	943.3	591.55M	-0.0276		44286	44286
8	03/30/2021	03/30/2021	980.75	985.76	988.68	972.99	485.20M	-0.007		44285	44285
9	03/29/2021	03/29/2021	987.66	976.09	999.17	976.03	486.11M	0.0145		44284	44284
10	03/26/2021	03/26/2021	973.52	973.14	976.7	965.26	382.36M	0.0034		44281	44281
11	03/25/2021	03/25/2021	970.2	967.06	976.4	953.76	582.99M	0.0032		44280	44280
12	03/24/2021	03/24/2021	967.07	977.25	980.52	963.2	374.71M	-0.0156		44279	44279
13	03/23/2021	03/23/2021	982.35	998.79	1002.6	980.37	480.02M	-0.019		44278	44278
14	03/22/2021	03/22/2021	1001.41	1005.33	1007.7	996.56	410.76M	-0.0045		44277	44277
15	03/19/2021	03/19/2021	1005.96	994.58	1008.22	992.35	546.46M	0.0099		44274	44274
16	03/18/2021	03/18/2021	996.09	983.87	998.25	983.75	399.37M	0.0156		44273	44273
17	03/17/2021	03/17/2021	980.79	979.11	986.5	976.92	418.84M	0.0018		44272	44272
18	03/16/2021	03/16/2021	979.08	981.26	986.53	974.83	419.43M	-0.0012		44271	44271
19	03/15/2021	03/15/2021	980.28	994.49	998.46	980.13	430.23M	-0.0125		44270	44270
20	44533	12/03/2021	992.71	969.73	993.14	967.73	379.61M	0.0309		44533	44533
21	44472	10/03/2021	962.99	948.31	966.25	944.15	319.30M	0.019		44472	44472
22	44442	09/03/2021	945.02	944.96	956.11	936.38	624.48M	0.0056		44442	44442
23	44411	08/03/2021	939.78	942.6	955.71	938.09	750.66M	0.0031		44411	44411
24	44319	05/03/2021	936.86	942.09	948.98	933.89	512.84M	-0.0112		44319	44319
25	44289	04/03/2021	947.46	963.36	963.85	947.46	424.67M	-0.0158		44289	44289
26	44258	03/03/2021	962.65	976.11	978.26	960.16	311.78M	-0.0109		44258	44258
27	44230	02/03/2021	973.26	959.15	975.43	956.7	476.57M	0.0222		44230	44230
28	44199	01/03/2021	952.11	957.08	958.84	939.9	581.20M	0.0011		44199	44199
29	02/26/2021	02/26/2021	951.1	951.18	957.2	933.57	486.14M	-0.011		44253	44253
30	02/25/2021	02/25/2021	961.66	958.12	964.7	953.95	565.20M	0.0089		44252	44252
31	02/24/2021	02/24/2021	953.19	980.18	983.48	947.67	577.56M	-0.0222		44251	44251
32	02/23/2021	02/23/2021	974.84	970.88	981.35	968.58	389.66M	0.0063		44250	44250
Scolty1985

Cell Formulas
Range	Formula
J2:K32	J2	=IF(ISNUMBER(A2),A2,DATE(TEXTAFTER(A2,"/",2),TEXTBEFORE(A2,"/",1),TEXTAFTER(TEXTBEFORE(A2,"/",2),"/",1)))
B2:B32	B2	=TEXT(A2,"mm/dd/yyyy")

 

[MARK858]

@scolty1985 what date format are your dates normally in? dd/mm/yyyy or mm/dd/yyyy?

 

[scolty1985]

Once you do the import use the TEXT TO COLUMNS procedure on your suspect date column. It should only reformat text that look like dates into numbers.
And as long as the date that the text is in is in a generally accepted date format it will convert it for you.
Its on the DATA RIBBON .... DATA>>DATATOOLS >> TEXT TO COLUMNS

if you have excel 365 or better you can use the text before and text after functions to parse out the year month and day into the DATE function if that doesn't work. Or maybe even in POWER QUERY, but I am not an expert on that.


Here is a suggestion using the DATE, TEXTBEFORE, and TEXTAFTER Functions:
Again, for 365 and later, I think. If you don't have the last two functions you'll have to use left, right, and mid to parse it out, like this:

=IF(ISNUMBER(A2),A2,DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2)))

I kept the column format as GENERAL, change to the date format as you desire.

Mr excel questions 58.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Day	Price	Open	High	Low	Vol.	Change %		Col A Conv	Col B Conv
2	44412	08/04/2021	975.58	965.99	975.58	962.1	654.72M	0.0116		44412	44412
3	44381	07/04/2021	964.41	963.04	968.66	957.95	538.83M	0.002		44381	44381
4	44351	06/04/2021	962.44	958.74	963.57	950.62	625.31M	0.0069		44351	44351
5	44320	05/04/2021	955.81	963.98	968.12	953.42	433.05M	-0.0023		44320	44320
6	44200	01/04/2021	957.99	953.17	963.06	948.93	359.68M	0.0045		44200	44200
7	03/31/2021	03/31/2021	953.66	979.3	979.31	943.3	591.55M	-0.0276		44286	44286
8	03/30/2021	03/30/2021	980.75	985.76	988.68	972.99	485.20M	-0.007		44285	44285
9	03/29/2021	03/29/2021	987.66	976.09	999.17	976.03	486.11M	0.0145		44284	44284
10	03/26/2021	03/26/2021	973.52	973.14	976.7	965.26	382.36M	0.0034		44281	44281
11	03/25/2021	03/25/2021	970.2	967.06	976.4	953.76	582.99M	0.0032		44280	44280
12	03/24/2021	03/24/2021	967.07	977.25	980.52	963.2	374.71M	-0.0156		44279	44279
13	03/23/2021	03/23/2021	982.35	998.79	1002.6	980.37	480.02M	-0.019		44278	44278
14	03/22/2021	03/22/2021	1001.41	1005.33	1007.7	996.56	410.76M	-0.0045		44277	44277
15	03/19/2021	03/19/2021	1005.96	994.58	1008.22	992.35	546.46M	0.0099		44274	44274
16	03/18/2021	03/18/2021	996.09	983.87	998.25	983.75	399.37M	0.0156		44273	44273
17	03/17/2021	03/17/2021	980.79	979.11	986.5	976.92	418.84M	0.0018		44272	44272
18	03/16/2021	03/16/2021	979.08	981.26	986.53	974.83	419.43M	-0.0012		44271	44271
19	03/15/2021	03/15/2021	980.28	994.49	998.46	980.13	430.23M	-0.0125		44270	44270
20	44533	12/03/2021	992.71	969.73	993.14	967.73	379.61M	0.0309		44533	44533
21	44472	10/03/2021	962.99	948.31	966.25	944.15	319.30M	0.019		44472	44472
22	44442	09/03/2021	945.02	944.96	956.11	936.38	624.48M	0.0056		44442	44442
23	44411	08/03/2021	939.78	942.6	955.71	938.09	750.66M	0.0031		44411	44411
24	44319	05/03/2021	936.86	942.09	948.98	933.89	512.84M	-0.0112		44319	44319
25	44289	04/03/2021	947.46	963.36	963.85	947.46	424.67M	-0.0158		44289	44289
26	44258	03/03/2021	962.65	976.11	978.26	960.16	311.78M	-0.0109		44258	44258
27	44230	02/03/2021	973.26	959.15	975.43	956.7	476.57M	0.0222		44230	44230
28	44199	01/03/2021	952.11	957.08	958.84	939.9	581.20M	0.0011		44199	44199
29	02/26/2021	02/26/2021	951.1	951.18	957.2	933.57	486.14M	-0.011		44253	44253
30	02/25/2021	02/25/2021	961.66	958.12	964.7	953.95	565.20M	0.0089		44252	44252
31	02/24/2021	02/24/2021	953.19	980.18	983.48	947.67	577.56M	-0.0222		44251	44251
32	02/23/2021	02/23/2021	974.84	970.88	981.35	968.58	389.66M	0.0063		44250	44250
Scolty1985

Cell Formulas
Range	Formula
J2:K32	J2	=IF(ISNUMBER(A2),A2,DATE(TEXTAFTER(A2,"/",2),TEXTBEFORE(A2,"/",1),TEXTAFTER(TEXTBEFORE(A2,"/",2),"/",1)))
B2:B32	B2	=TEXT(A2,"mm/dd/yyyy")

The text to columns function worked a treat, thank you very much

 

[awoohaw]

You're welcome.
I'm pleased you found a solution!

Best Wishes!