dates are not formatted as date but as general

[lezawang]

Hi
I download the data below from this website https://www.nasdaq.com/market-activity/stocks/aapl/historical . You can see some date are formatted as date and some as a text (general). I tried to copy number 1 and the use pasteSpecial and select multiple but that did not help. I am curious, how you would fix that? Thank you very much.

PS oh wow MrExcel add in converted all dates into number (right hand side) but that is not the case in real life. please see the screen shot attached

HistoricalData_1632168812979.csv
	A	B	C	D	E	F
1	Date	Close/Last	Volume	Open	High	Low
2	09/17/2021	$146.06	1.3E+08	$148.82	$148.82	$145.76
3	09/16/2021	$148.79	68034150	$148.44	$148.97	$147.22
4	09/15/2021	$149.03	83281320	$148.56	$149.44	$146.37
5	09/14/2021	$148.12	1.09E+08	$150.35	$151.07	$146.91
6	09/13/2021	$149.55	1.02E+08	$150.63	$151.42	$148.75
7	09/10/2021	$148.97	1.41E+08	$155	$155.48	$148.70
8	09/09/2021	$154.07	57305730	$155.49	$156.11	$153.95
9	09/08/2021	$155.11	74420210	$156.98	$157.04	$153.98
10	09/07/2021	$156.69	82278260	$154.97	$157.26	$154.39
11	09/03/2021	$154.30	57866070	$153.76	$154.63	$153.09
12	09/02/2021	$153.65	71171320	$153.87	$154.72	$152.40
13	09/01/2021	$152.51	80313710	$152.83	$154.98	$152.34
14	08/31/2021	$151.83	86453120	$152.66	$152.80	$151.29
15	08/30/2021	$153.12	90956720	$149	$153.49	$148.61
16	08/27/2021	$148.60	55802390	$147.48	$148.75	$146.83
17	08/26/2021	$147.54	48597200	$148.35	$149.12	$147.51
18	08/25/2021	$148.36	58991300	$149.81	$150.32	$147.80
19	08/24/2021	$149.62	48606430	$149.45	$150.86	$149.15
20	08/23/2021	$149.71	60131810	$148.31	$150.19	$147.89
21	08/20/2021	$148.19	60549630	$147.44	$148.50	$146.78
22	08/19/2021	$146.70	86960310	$145.03	$148	$144.50
23	08/18/2021	$146.36	86325990	$149.80	$150.72	$146.15
24	08/17/2021	$150.19	92229740	$150.23	$151.68	$149.09
25	08/16/2021	$151.12	1.04E+08	$148.54	$151.19	$146.47
26	08/13/2021	$149.10	59375010	$148.97	$149.44	$148.27
27	08/12/2021	$148.89	73779110	$146.19	$149.05	$145.84
28	08/11/2021	$145.86	48493460	$146.05	$146.72	$145.53
29	08/10/2021	$145.60	69023080	$146.44	$147.71	$145.30
30	08/09/2021	$146.09	48908690	$146.20	$146.70	$145.52
HistoricalData_1632168812979

 

[alansidman]

How are you getting the data? Power Query? VBA? Some other method?

 

[lezawang]

How are you getting the data? Power Query? VBA? Some other method?

Thank for your reply. No just clicked on download and select for how many days and then I will get the CSV file. Thank you.

 

[alansidman]

Leza,
When I replicated your steps by downloading, all dates came in the correct format. My excel defaults are set for US. Are yours? If yours are different, then once you have brought the data into Excel, then upload to Power Query and let it change the values to your preferred format.

HistoricalData_1632174486691.csv
	A	B	C	D	E	F
1	Date	Close/Last	Volume	Open	High	Low
2	9/17/2021	$146.06	129868800	$148.82	$148.82	$145.76
3	9/16/2021	$148.79	68034150	$148.44	$148.97	$147.22
4	9/15/2021	$149.03	83281320	$148.56	$149.44	$146.37
5	9/14/2021	$148.12	109296300	$150.35	$151.07	$146.91
6	9/13/2021	$149.55	102404300	$150.63	$151.42	$148.75
7	9/10/2021	$148.97	140893200	$155	$155.48	$148.70
8	9/9/2021	$154.07	57305730	$155.49	$156.11	$153.95
9	9/8/2021	$155.11	74420210	$156.98	$157.04	$153.98
10	9/7/2021	$156.69	82278260	$154.97	$157.26	$154.39
11	9/3/2021	$154.30	57866070	$153.76	$154.63	$153.09
12	9/2/2021	$153.65	71171320	$153.87	$154.72	$152.40
13	9/1/2021	$152.51	80313710	$152.83	$154.98	$152.34
14	8/31/2021	$151.83	86453120	$152.66	$152.80	$151.29
15	8/30/2021	$153.12	90956720	$149	$153.49	$148.61
16	8/27/2021	$148.60	55802390	$147.48	$148.75	$146.83
17	8/26/2021	$147.54	48597200	$148.35	$149.12	$147.51
18	8/25/2021	$148.36	58991300	$149.81	$150.32	$147.80
19	8/24/2021	$149.62	48606430	$149.45	$150.86	$149.15
20	8/23/2021	$149.71	60131810	$148.31	$150.19	$147.89
21	8/20/2021	$148.19	60549630	$147.44	$148.50	$146.78
HistoricalData_1632174486691

 

[lezawang]

Thank you very much. I changed my computer date to MM-DD-YYYY and it worked fine. Thank you so much again.