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
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,626
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

joeu2004

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

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Nov 22, 2018
Messages
11

ADVERTISEMENT

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
Your date is incorrect, it's listing the year as day and the day as year.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,626
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Did you try any of the suggestions in Post # 11?
 

Dandada2602

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,315
Messages
5,623,950
Members
416,001
Latest member
teabag

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