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

 

[joeu2004]

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

Be specific: show actual formulas that you tried, as well as actual data with cell names (an XL2BB capture is best).

In addition to an XL2BB capture, I suggest that you upload an example Excel file (redacted) that demonstrates the problems to a file-sharing website, and post the download URL in a response here. I like box.net/files; others like dropbox.com. In any case, test the download URL first, being careful to log out of all websites that share the same login, in order to ensure that anyone else can access the file anonymously.

(Some contributors might object because they cannot or will not download files. That's why you should also provide the XL2BB capture, even though that might not have sufficient detail to troubleshoot this particular problem.)

-----

We can only guess that your system date form (specified in the Region/Language control panel) is __not__ yy/mm/dd, which seems to be the form of your data.

In that case, your data is type text. Confirm with formulas of the form =ISTEXT(A1). Looks can be deceiving; and the cell format does not matter.

You might be able to use the Text To Column feature to convert the data. In the third (final) dialog box, select YMD for the form of the column.

Alternatively, in a parallel column, enter formulas of the form:

=DATE(2000+LEFT(A1,2), MID(A1,4,2), MID(A1,7,2)) + TIMEVALUE(RIGHT(A1,5))

formatted as date and time according to your preference.

 

[etaf]

do you only want dates and not the times ?

what did you do for left/right/mid ?

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

 

[Dandada2602]

I tried your formula but it didn't worked. I tried =DATE(YEAR(LEFT(A1,2)*1),MONTH(MID(A1,4,2)*1),DAY(MID(A1,7,2)*1))+TIMEVALUE(MID(A1,10,5)).

 

[etaf]

did you confirm re questions in

joeu2004​

post

Also Left A2,2
= 21
and so that is year 21 NOT 2021
see

joeu2004​

Post
Or my change to use datevalue and put the year last, then /21 will be 2021

see example below - TEXT to real date and time

Book2
	A	B	C
1	21/10/09 12:15:00		10/9/21 12:15
Sheet1

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

 

[joeu2004]

I tried your formula but it didn't worked. I tried =DATE(YEAR(LEFT(A1,2)*1),MONTH(MID(A1,4,2)*1),DAY(MID(A1,7,2)*1))+TIMEVALUE(MID(A1,10,5)).

That is not __my__ formula. Did you try mine __exactly__ as I wrote it (with appropriate cell reference)?

Your formula fails because the YEAR, MONTH and DAY functions expect a bona fide date (text or numeric) as a parameter. Instead, you are passing the number 21, 2 and 12, for example.

In contrast, my formula effectively has the expression DATE(2000+"21", "2", "12"), which Excel correctly interprets as DATE(2021, 2, 12).

I cannot help you further. Good luck!

 

[Dandada2602]

Here's a data sample that doesn't work:

YY-MM-DD HH-MM convert to date.xlsx
	A	B	C	D
1	Incoming Time	Connection Time	Convert	Convert 2
2	21/02/12 20:31	21/02/12 20:49	#VALUE!	1/12/1900
3	21/02/12 22:09	21/02/12 22:55	#VALUE!	1/12/1900
4	21/02/12 15:34	21/02/12 16:05	#VALUE!	1/12/1900
5	21/02/15 21:18	21/02/15 21:47	#VALUE!	1/15/1900
6	21/02/17 22:06	21/02/17 22:24	#VALUE!	1/17/1900
7	21/02/15 21:37	21/02/15 22:06	#VALUE!	1/15/1900
8	21/02/15 16:05	21/02/15 16:14	#VALUE!	1/15/1900
9	21/02/17 20:53	21/02/17 21:07	#VALUE!	1/17/1900
10	21/02/17 00:55	21/02/17 01:18	#VALUE!	1/17/1900
11	21/02/17 02:34	21/02/17 03:07	#VALUE!	1/17/1900
12	21/02/19 05:31	21/02/19 05:43	#VALUE!	1/19/1900
13	21/02/19 12:46	21/02/19 12:56	#VALUE!	1/19/1900
14	21/02/19 18:44	21/02/19 19:11	#VALUE!	1/19/1900
15	21/02/19 09:18	21/02/19 09:34	#VALUE!	1/19/1900
16	21/02/16 17:00	21/02/16 17:35	#VALUE!	1/16/1900
17	21/02/18 22:50	21/02/18 22:59	#VALUE!	1/18/1900
18	21/02/19 20:36	21/02/19 21:17	#VALUE!	1/19/1900
19	21/02/19 22:26	21/02/20 00:50	#VALUE!	1/19/1900
Sheet1

Cell Formulas
Range	Formula
C2:C19	C2	=DATEVALUE(MID(A2,7,2)&"/"&MID(A2,4,2)&"/"&LEFT(A2,2))+TIMEVALUE(RIGHT(A2,8))
D2	D2	=DATE(YEAR(LEFT(A2,2)*1),MONTH(MID(A2,4,2)),DAY(MID(A2,7,2)*1))+TIMEVALUE(MID(A2,10,5))
D3:D19	D3	=DATE(YEAR(LEFT(A3,2)*1),MONTH(MID(A3,4,2)*1),DAY(MID(A3,7,2)*1))+TIMEVALUE(MID(A3,10,5))

 

[joeu2004]

=DATEVALUE(MID(A1,7,2)&"/"&MID(A1,4,2)&"/"&LEFT(A1,2))

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.

 

[xenou]

I tried your formula but it didn't worked. I tried =DATE(YEAR(LEFT(A1,2)*1),MONTH(MID(A1,4,2)*1),DAY(MID(A1,7,2)*1))+TIMEVALUE(MID(A1,10,5)).

This should have been:
=DATE((LEFT(A1,2)*1),(MID(A1,4,2)*1),MID(A1,7,2)*1))+TIMEVALUE(MID(A1,10,5))

No need for year, month, day formulas.

 

[Dave Patton]

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