Date Text Conversation

[mahmed1]

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

 

[Joe4]

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:

=INT(A1)

and then format that cell with the dd/mm/yy date format.

 

[johnnyL]

=TEXT(A1, "DD/MM/YY")

 

[Joe4]

=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]

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
	A	B
1	6/14/21 12:00 AM	14/06/2021
2	6/5/21 01:00	05/06/2021
Hoja9

Cell Formulas
Range	Formula
B1:B2	B1	=DATE(20&MID(A1,FIND(" ",A1)-2,2), LEFT(A1,FIND("/",A1)-1), TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",99)),99,99)))

 

[Joe4]

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:

=INT(A1+0)

and format the result?

 

[DanteAmor]

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
	A	B
1	6/14/21 12:00 AM	#¡VALOR!
Hoja9

Cell Formulas
Range	Formula
B1	B1	=INT(A1+0)

 

[Toadstool]

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
	D	E	F
1	Date	Converted	With Time
2	6/14/21 12:00 AM	14/06/21	14/06/21 12:00 AM
3	6/14/21 01:00 AM	14/06/21	14/06/21 01:00 AM
4	12/14/21 01:00 AM	14/12/21	14/12/21 01:00 AM
5	6/4/21 11:00 PM	04/06/21	04/06/21 11:00 PM
6	11/11/21 01:00 AM	11/11/21	11/11/21 01:00 AM
7	6/14/21 01:00 AM	14/06/21	14/06/21 01:00 AM
8	1/1/22 01:00 AM	01/01/22	01/01/22 01:00 AM
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=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:F8	F2	=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),"")

 

[Joe4]

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

Probably due to regional settings, as it works for me.

 

[DanteAmor]

Probably due to regional settings, as it works for me.

It is probably a problem that the OP is having