Date Text Conversation

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,253
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hiya

i have a column of data (column A) that has text values like this

6/14/21 12:00 AM
6/14/21 01:00 AM etc

i have tried to do a text to column - did a find replace - did + 0 to try and make it into a date but nothings working

what i want to do is just get the date in dd/mm/yy format

eg 14/06/21

can someone please help me get around this annoying problem

thank you
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,851
Office Version
  1. 365
Platform
  1. Windows
Dates are stored as numbers in Excel.
The days are the number of whole days since 1/0/1900.
The time is just the fraction of one day.

One way to remove the time is to use the INT function on the cell, and then apply your desired date format.
So if the value is in cell A1, use:
Excel Formula:
=INT(A1)
and then format that cell with the dd/mm/yy date format.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,096
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
=TEXT(A1, "DD/MM/YY")
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,851
Office Version
  1. 365
Platform
  1. Windows
=TEXT(A1, "DD/MM/YY")
Just note that will return a TEXT value, not a valid DATE value.
So if you try to use it in sorting, or try to use it in any sort of date calculations, it will not work properly.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,173
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

If it cannot be converted to date because it is a text, then you could use the following to make a date with year 20nn

varios 15jul2021.xlsm
AB
16/14/21 12:00 AM14/06/2021
26/5/21 01:0005/06/2021
Hoja9
Cell Formulas
RangeFormula
B1:B2B1=DATE(20&MID(A1,FIND(" ",A1)-2,2), LEFT(A1,FIND("/",A1)-1), TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",99)),99,99)))
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,851
Office Version
  1. 365
Platform
  1. Windows
If it cannot be converted to date because it is a text, then you could use the following to make a date with year 20nn
Wouldn't it just be simpler to use:
Excel Formula:
=INT(A1+0)
and format the result?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,173
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Wouldn't it just be simpler to use:

It doesn't work for me if in cell A1 I have a text:

varios 15jul2021.xlsm
AB
16/14/21 12:00 AM#¡VALOR!
Hoja9
Cell Formulas
RangeFormula
B1B1=INT(A1+0)
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,443
Office Version
  1. 2016
Platform
  1. Windows
Hi Mahmed1,

I suspect your locale does not use the US m/d/yy date format in which case if you've tried the Text to Columns wizard it does have an option to change date formats you might try.

This horribly complex formula should also do it:

Mahmed1.xlsx
DEF
1DateConvertedWith Time
26/14/21 12:00 AM14/06/2114/06/21 12:00 AM
36/14/21 01:00 AM14/06/2114/06/21 01:00 AM
412/14/21 01:00 AM14/12/2114/12/21 01:00 AM
56/4/21 11:00 PM04/06/2104/06/21 11:00 PM
611/11/21 01:00 AM11/11/2111/11/21 01:00 AM
76/14/21 01:00 AM14/06/2114/06/21 01:00 AM
81/1/22 01:00 AM01/01/2201/01/22 01:00 AM
Sheet1
Cell Formulas
RangeFormula
E2:E8E2=IFERROR(RIGHT("00"&SUBSTITUTE(MID(D2,FIND("/",D2)+1,2),"/",""),2)&"/"&RIGHT("00"&LEFT(D2,FIND("/",D2)-1),2)&"/"&MID(D2,FIND("/",D2,4)+1,2),"")
F2:F8F2=IFERROR(RIGHT("00"&SUBSTITUTE(MID(D2,FIND("/",D2)+1,2),"/",""),2)&"/"&RIGHT("00"&LEFT(D2,FIND("/",D2)-1),2)&"/"&MID(D2,FIND("/",D2,4)+1,2)&" "&RIGHT(D2,8),"")
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,971
Messages
5,767,389
Members
425,410
Latest member
SmittyT

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