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

Dandada2602

New Member
Joined
Nov 22, 2018
Messages
11
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,014
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Oct 24, 2012
Messages
4,578
Office Version
  1. 365
Platform
  1. MacOS
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

New Member
Joined
Nov 22, 2018
Messages
11
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

Well-known Member
Joined
Oct 24, 2012
Messages
4,578
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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
ABC
121/10/09 12:15:0010/9/21 12:15
Sheet1
Cell Formulas
RangeFormula
C1C1=DATEVALUE(MID(A1,7,2)&"/"&MID(A1,4,2)&"/"&LEFT(A1,2))+TIMEVALUE(RIGHT(A1,8))
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,014
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Nov 22, 2018
Messages
11

ADVERTISEMENT

Here's a data sample that doesn't work:
YY-MM-DD HH-MM convert to date.xlsx
ABCD
1Incoming TimeConnection TimeConvertConvert 2
221/02/12 20:3121/02/12 20:49#VALUE!1/12/1900
321/02/12 22:0921/02/12 22:55#VALUE!1/12/1900
421/02/12 15:3421/02/12 16:05#VALUE!1/12/1900
521/02/15 21:1821/02/15 21:47#VALUE!1/15/1900
621/02/17 22:0621/02/17 22:24#VALUE!1/17/1900
721/02/15 21:3721/02/15 22:06#VALUE!1/15/1900
821/02/15 16:0521/02/15 16:14#VALUE!1/15/1900
921/02/17 20:5321/02/17 21:07#VALUE!1/17/1900
1021/02/17 00:5521/02/17 01:18#VALUE!1/17/1900
1121/02/17 02:3421/02/17 03:07#VALUE!1/17/1900
1221/02/19 05:3121/02/19 05:43#VALUE!1/19/1900
1321/02/19 12:4621/02/19 12:56#VALUE!1/19/1900
1421/02/19 18:4421/02/19 19:11#VALUE!1/19/1900
1521/02/19 09:1821/02/19 09:34#VALUE!1/19/1900
1621/02/16 17:0021/02/16 17:35#VALUE!1/16/1900
1721/02/18 22:5021/02/18 22:59#VALUE!1/18/1900
1821/02/19 20:3621/02/19 21:17#VALUE!1/19/1900
1921/02/19 22:2621/02/20 00:50#VALUE!1/19/1900
Sheet1
Cell Formulas
RangeFormula
C2:C19C2=DATEVALUE(MID(A2,7,2)&"/"&MID(A2,4,2)&"/"&LEFT(A2,2))+TIMEVALUE(RIGHT(A2,8))
D2D2=DATE(YEAR(LEFT(A2,2)*1),MONTH(MID(A2,4,2)),DAY(MID(A2,7,2)*1))+TIMEVALUE(MID(A2,10,5))
D3:D19D3=DATE(YEAR(LEFT(A3,2)*1),MONTH(MID(A3,4,2)*1),DAY(MID(A3,7,2)*1))+TIMEVALUE(MID(A3,10,5))
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,014
Office Version
  1. 2010
Platform
  1. Windows
=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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,824
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Feb 15, 2002
Messages
4,626
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
ABCD
1Incoming TimeConnection TimeConvertConvert 2
221-Feb-2012 20:3121-Feb-2012 20:4921-Feb-2012 20:3121-Feb-2012 20:49
321-Feb-2012 22:0921-Feb-2012 22:5521-Feb-2012 22:0921-Feb-2012 22:55
421-Feb-2012 15:3421-Feb-2012 16:0521-Feb-2012 15:3421-Feb-2012 16:05
521-Feb-2015 21:1821-Feb-2015 21:4721-Feb-2015 21:1821-Feb-2015 21:47
621-Feb-2017 22:0621-Feb-2017 22:2421-Feb-2017 22:0621-Feb-2017 22:24
721-Feb-2015 21:3721-Feb-2015 22:0621-Feb-2015 21:3721-Feb-2015 22:06
821-Feb-2015 16:0521-Feb-2015 16:1421-Feb-2015 16:0521-Feb-2015 16:14
921-Feb-2017 20:5321-Feb-2017 21:0721-Feb-2017 20:5321-Feb-2017 21:07
1021-Feb-2017 00:5521-Feb-2017 01:1821-Feb-2017 00:5521-Feb-2017 01:18
1121-Feb-2017 02:3421-Feb-2017 03:0721-Feb-2017 02:3421-Feb-2017 03:07
1221-Feb-2019 05:3121-Feb-2019 05:4321-Feb-2019 05:3121-Feb-2019 05:43
1321-Feb-2019 12:4621-Feb-2019 12:5621-Feb-2019 12:4621-Feb-2019 12:56
1421-Feb-2019 18:4421-Feb-2019 19:1121-Feb-2019 18:4421-Feb-2019 19:11
1521-Feb-2019 09:1821-Feb-2019 09:3421-Feb-2019 09:1821-Feb-2019 09:34
1621-Feb-2016 17:0021-Feb-2016 17:3521-Feb-2016 17:0021-Feb-2016 17:35
1721-Feb-2018 22:5021-Feb-2018 22:5921-Feb-2018 22:5021-Feb-2018 22:59
1821-Feb-2019 20:3621-Feb-2019 21:1721-Feb-2019 20:3621-Feb-2019 21:17
1921-Feb-2019 22:2621-Feb-2020 00:5021-Feb-2019 22:2621-Feb-2020 00:50
6cc
Cell Formulas
RangeFormula
C2:D19C2=A2
 

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,976
Members
416,003
Latest member
indyman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top