having problems changing the date format

Hi All,

i'm currently trying to convert 12/18/2015 into 2015-12-18 using the text() formula.

currently cell DI73 is populated with 12/18/2015
1. therefore i use the formula, =TEXT(DI73,"yyyy-mm-dd") but i still get 12/18/2015.

2. i also tried changing the format manually as shown in the attachment, but still unable to change the format.

date issue.png

3. i also ensured that the computer date format is also in yyyy-mm-dd format, but still nothing works.

i am really stuck here.

Originally Posted by shukla.ankur281190

Once try to select the whole column where dates are mentioned then CTRL+H -> find "/" and Replace with "-".

but i want to put the year in front. replacing only solves the problem halfway.

Originally Posted by TMS

If you format cell D173 as General, what happens? Does it change to a numeric value, or does it stay the same? If it changes to a number, it is a date and should respond to date formatting. If it doesn't, it is a text value that just looks like a date and won't be affected by formatting or using TEXT().


Regards, TMS

Thanks for the highlight. but how do i convert the text to a date format? i tried datevalue and date. nothing seems to work.

Originally Posted by shukla.ankur281190

Try
E173=RIGHT(D173,4)&"-"&IF(LEN(D173)>=10,LEFT(D173,2),"0"&LEFT(D173,1))&"-"&IF(LEN(D173)>=10,MID(D173,FIND("/",D173)+1,2),"0"&MID(D173,FIND("/",D173)+1,1))

Formula:

Please Login or Register  to view this content.

thanks man. but i'm trying to replace all dates with the new format in its own cell. if it were in a different cell i would have used your method as well. should i be using a macro for this?

Hi,

Try if this formula works for you

=TEXT(DATE(VALUE(RIGHT(TEXT(LEFT(D173,10),"dd/mm/yyyy"),4)),VALUE(LEFT(TEXT(LEFT(D173,10),"dd/mm/yyyy"),FIND("/",TEXT(LEFT(D173,10),"dd/mm/yyyy"))-1)),VALUE(MID(TEXT(LEFT(D173,10),"dd/mm/yyyy"),FIND("/",TEXT(LEFT(D173,10),"dd/mm/yyyy"))+1,2))),"yyyy-mm-dd")

Text-to-columns will work

Custom format your column to YYYY-MM-DD.

Text-To-Columns

In Step 3

Choose Date

Format MDY

Finish

It will show as "dd/mm/yyyy" in formula bar but as "yyyy-mm-dd" in the column cells

Hi JohnTopley,

Yup, tried the text to column thing, it changed it into 3 cell columns. but the question is how do i convert it and maintain a single cell? i do not want it to split into 3 columns. and also the date value is wrong in each column. sorry i'm new to this text to column function.

2015-12-22 4-17-17 PM.png

Hi NinadM,

i tried the formula and it worked just fine. but how do i replace each text with your formula for the entire worksheet which contains hundreds of rows and columns? i do not want to create a new column beside it to place the formula into. any ideas?

It should only be ONE column: just go straight to Step 3 in Text-_to-Columns (ignore steps 1 and 2)

Post a file NOT image: we cannot work with an image