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

D

Dandada2602

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

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Dandada2602 said:
I have tried using DATEVALUE, TEXT, DATE+TIMEVALUE, LEFT, MID, RIGHT etc. and Just don't get what is missing on this conversion.
Click to expand...

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.
 
Upvote 0
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))
 
Upvote 0
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)).
 
Upvote 0
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))
 
Upvote 0
Dandada2602 said:
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)).
Click to expand...

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!
 
Upvote 0
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))
 
Upvote 0
etaf said:
=DATEVALUE(MID(A1,7,2)&"/"&MID(A1,4,2)&"/"&LEFT(A1,2))
Click to expand...

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.
 
Upvote 0
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)).
Click to expand...
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

muhammad susanto
  • Solved
Convert Format Date From Mm/Dd/Yyyy to Dd/Mm/Yyyy
2
Replies
15
Views
2K
nisanthp
N
charlesstricklin
  • Solved
aturday, March 16th, 2024 to MM/DD/YY?
Replies
5
Views
614
Peter_SSs
Peter_SSs
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
600
awoohaw
awoohaw
C
  • Question Question
ODD DATE & TIME (FORMAT) CALCULATION
Replies
8
Views
1K
Tetra201
Tetra201
JenniferMurphy
  • Solved
Adding a new time causes wrong average
Replies
3
Views
275
awoohaw
awoohaw

Forum statistics

Threads
1,223,886
Messages
6,175,190
Members
452,616
Latest member
intern444

Share this page

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
Back
Top