Unable to convert date+time (yy/mm/dd hh:mm) format to excel date

[Dandada2602]

I have the following column with dates, that I need to convert for excel to recognize them as dates:

21/02/12 15:34

21/02/15 21:18

21/02/17 22:06

21/02/15 21:37

21/02/15 16:05

21/02/17 20:53

21/02/17 00:55

21/02/17 02:34

21/02/19 05:31

I have tried using DATEVALUE, TEXT, DATE+TIMEVALUE, LEFT, MID, RIGHT etc. and Just don't get what is missing on this conversion.

Thanks in advance for your help.

Dandada2602

 

[Dave Patton]

Date and Time 2021.xlsm
	A	B	C
1			Format
2	Value	21-02-15 21:18	Feb 21, 2015
3	Value	21-02-12 15:34	Feb 21, 2012
4
5	Text	21-02-15 21:18	Feb 21, 2015
6	Text	21-02-15 15:34	Feb 21, 2015
7
8	Text	21-02-15 21:18	Feb 15, 2021
9	Text	21-02-15 15:34	Feb 15, 2021
10
11	Text	21-02-15 21:18	Feb 15, 2021
12	Text	21-02-15 15:34	Feb 15, 2021
13
6c

Cell Formulas
Range	Formula
C2:C3	C2	=B2
C5:C6	C5	=--LEFT(B5,8)
C8:C9	C8	=DATE(20&LEFT(B8,2),MID(B8,4,2),MID(B8,7,2))
C11:C12	C11	=DATE(2000+LEFT(B11,2),MID(B11,4,2),MID(B11,7,2))

 

[joeu2004]

=DATE((LEFT(A1,2)*1),(MID(A1,4,2)*1),MID(A1,7,2)*1))+TIMEVALUE(MID(A1,10,5))

It is not necessary to convert the LEFT, MID and RIGHT extractions to numbers.

And DATE(21,...) is interpreted as the year 1921, not 2021, assuming default setting for 2-digit years. Well, on my computer, at least.

 

[Dandada2602]

Does that work for all regional configurartions? Or you making the assumption that the OP's computer recognizes 12/2/21 (DMY) as a date?

I just tested DATEVALUE("12/02/21") on my MDY computer, and I confirmed that it is interpreted as Dec 2 2021, not Feb 12 2021, as intended.

My regional settings are set to United States

 

[Rick Rothstein]

If none of your dates will ever be in the 1900s, then you can use this formula...

=0+(20&SUBSTITUTE(A1,"/","-"))

You will then be able to format the cell containing the formula to display the date however you want.

 

[Dandada2602]

Your post shows Dates and time. You can just format to your preference.
You may not need Columns C and D

Date and Time 2021.xlsm
	A	B	C	D
1	Incoming Time	Connection Time	Convert	Convert 2
2	21-Feb-2012 20:31	21-Feb-2012 20:49	21-Feb-2012 20:31	21-Feb-2012 20:49
3	21-Feb-2012 22:09	21-Feb-2012 22:55	21-Feb-2012 22:09	21-Feb-2012 22:55
4	21-Feb-2012 15:34	21-Feb-2012 16:05	21-Feb-2012 15:34	21-Feb-2012 16:05
5	21-Feb-2015 21:18	21-Feb-2015 21:47	21-Feb-2015 21:18	21-Feb-2015 21:47
6	21-Feb-2017 22:06	21-Feb-2017 22:24	21-Feb-2017 22:06	21-Feb-2017 22:24
7	21-Feb-2015 21:37	21-Feb-2015 22:06	21-Feb-2015 21:37	21-Feb-2015 22:06
8	21-Feb-2015 16:05	21-Feb-2015 16:14	21-Feb-2015 16:05	21-Feb-2015 16:14
9	21-Feb-2017 20:53	21-Feb-2017 21:07	21-Feb-2017 20:53	21-Feb-2017 21:07
10	21-Feb-2017 00:55	21-Feb-2017 01:18	21-Feb-2017 00:55	21-Feb-2017 01:18
11	21-Feb-2017 02:34	21-Feb-2017 03:07	21-Feb-2017 02:34	21-Feb-2017 03:07
12	21-Feb-2019 05:31	21-Feb-2019 05:43	21-Feb-2019 05:31	21-Feb-2019 05:43
13	21-Feb-2019 12:46	21-Feb-2019 12:56	21-Feb-2019 12:46	21-Feb-2019 12:56
14	21-Feb-2019 18:44	21-Feb-2019 19:11	21-Feb-2019 18:44	21-Feb-2019 19:11
15	21-Feb-2019 09:18	21-Feb-2019 09:34	21-Feb-2019 09:18	21-Feb-2019 09:34
16	21-Feb-2016 17:00	21-Feb-2016 17:35	21-Feb-2016 17:00	21-Feb-2016 17:35
17	21-Feb-2018 22:50	21-Feb-2018 22:59	21-Feb-2018 22:50	21-Feb-2018 22:59
18	21-Feb-2019 20:36	21-Feb-2019 21:17	21-Feb-2019 20:36	21-Feb-2019 21:17
19	21-Feb-2019 22:26	21-Feb-2020 00:50	21-Feb-2019 22:26	21-Feb-2020 00:50
6cc

Cell Formulas
Range	Formula
C2:D19	C2	=A2

Your date is incorrect, it's listing the year as day and the day as year.

 

[Dave Patton]

Did you try any of the suggestions in Post # 11?

 

[Dandada2602]

If none of your dates will ever be in the 1900s, then you can use this formula...

=0+(20&SUBSTITUTE(A1,"/","-"))

You will then be able to format the cell containing the formula to display the date however you want.

You did it!! Awesome!!

Can you please explain so I don't feel so dumb

 

[Rick Rothstein]

A universally recognized date format is YYYY-MM-DD so all I did is substitute your slash characters for dashes and concatenated a 20 in front to complete the required 4-digit year, then I added 0 to force Excel to convert the numeric (dates are numbers) text string to a real number (date serial value) so that you could then formatted it.