MS Excel 2007 not formatting dates

T

theterminator

Wise Old Owl
I have a list of dates which I want to convert to format DD-MON-YYYY but when I select all columns then excel doesn't convert all cells. Few cells are not converted even individually selecting them. Please help

Also please tell how to remove excel from auto formatting dates.
Sent from my iPhone using Tapatalk
 
nac

nac

Aspiring Novelist
If you copy/paste from some other source, the pasted content sometimes pasted as text or general. Even after formatting as date, they won't change. In that case, after formatting you have to edit the cells (press F2 then press enter). It should work. If not post the file or sample...

Other work around is, esp. if you have large amount of data (assuming they are in the same column continuously), copy those date and paste it in notepad. Again copy from notepad and paste it in excel.
 
OP
T

theterminator

Wise Old Owl
nac said:
If you copy/paste from some other source, the pasted content sometimes pasted as text or general. Even after formatting as date, they won't change. In that case, after formatting you have to edit the cells (press F2 then press enter). It should work. If not post the file or sample...

Other work around is, esp. if you have large amount of data (assuming they are in the same column continuously), copy those date and paste it in notepad. Again copy from notepad and paste it in excel.
Click to expand...

i did it in one cell, i cleared contents by from context menu and then fed data as fresh but still it doesn't recognize


Sent from my iPhone using Tapatalk
 
Flash

Flash

Lost in speed
Open new excel, copy paste the dates from the original excel, format the dates in the format you want.
Once done, copy paste back to the original excel.

Experienced once.
 
patkim

patkim

Cyborg Agent
Most likely your data (cells) are formatted as Text, hence even setting it to a Date format does not work.
What is the original format the date is? Try using Excel built in function named DATEVALUE

Say your date in whatever format e.g. m/d/yyyy is in a cell A1 then in a blank cell use function =DATEVALUE(A1) this may result into a number. Now on that cell apply DD-MMM-YYYY format and check if it works.
 
OP
T

theterminator

Wise Old Owl
sample sheet.xlsx - Google Driv

i have copy pasted the dates column to a new workbook here

- - - Updated - - -

I have one more sheet where I have integers starting from 1 which I want sorted ascending but they are sorted like 1, 10, 100, 1000, 1001,1002,1003,1004,1005,1006,1007,1008,1009,101,1010....and so on. How to correct this?
 
patkim

patkim

Cyborg Agent
Regarding your sorting, it's formatted as Text hence they are sorted A-Z not 1-10. Use VALUE function to convert text to numbers and the Sort on that new column.
 
OP
T

theterminator

Wise Old Owl
patkim said:
Regarding your sorting, it's formatted as Text hence they are sorted A-Z not 1-10. Use VALUE function to convert text to numbers and the Sort on that new column.
Click to expand...

how do i use the value function? I want to apply for a whole column, what parameters should i have to enter? If i enter cell number then a 0 is automatically created in those cells which are empty.
Actually i have two columns, the main column has integers with a "v" alphabet at the left, ex: v1,v2,v3.....etc. My objective is to sort this column for which I created another column using MID function which got rid of the "v" alphabet and only stored the integers. So, if I sort this integer only column then my job is done.
 
patkim

patkim

Cyborg Agent
Before you sort, you will have to get rid of blanks. A blank cell in a column stops Sort and it does not proceed thereafter.
You need to use a helper column say B adjacent to your main column where data is present and use VALUE(Cell ref) e.g.VALUE(A1) in the next cell and drag it down. Now if you have numbers as text in Column A they shall get converted to numbers internally by VALUE function. Now copy the column B back to your original column A and sort A on ascending.
 
nac

nac

Aspiring Novelist
theterminator said:
i have copy pasted the dates column to a new workbook here
Click to expand...
It's working for me. It's formatted in dd mmmm yyyy
Despite the formatting some cells are not displaying the date the way you want. Did you try what I said in my previous comments? I just edited (F2 and enter), it's working. And also the notepad copy/paste method is also working for me. Here are the snapshots

*preview.ibb.co/ezzbmQ/Microsoft_Excel_sample_sheet_2017_04_26_01_55_54.png
 
You must log in or register to reply here.
Top Bottom