Cells are NOT showing date as per Format Cell?

[port80]

In my exel sheet, I have selected date in Format Cells as 3/14/01

some cells in DATE column are showing date in correct format like
5/31/12
while some are showing it as
31/5/12

When I select any Date Cell like shown 1/6/12,
date on top data entry section appear as 06/01/2012

Any suggestion pelase?

thanks.

 

[cyrilbrd]

Hi,

what format would you like to have? mm/dd/yy or dd/mm/yy ?
to convert 31/5/2012 into a date you could use such as:

=DATE(RIGHT(A2,4)+0,MID(A2,FIND("/",A2,1)+1,
FIND("/",A2,FIND("/",A2,1)+1)-FIND("/",A2,1)-1)+0,
LEFT(A2,FIND("/",A2,1)-1)+0)

given that the above mentioned date is in A2 (adjust to fit).

the format of the result could then be set to fit your needs eg:
d-mmm-yy for 31-May-12
m/d/yy for 5/31/12
d-mmm-yyy for 31-May-2012 ect.

is this helping?

 

[port80]

My selected data format from cell format properties is 3/14/01
which is M/DD/YY

this format is applied on all cells of date column

some cells show data format as
M/DD/YY
while some shows and convert it as
D/MM/YY

Using MS Excel 2010

Any suggestion please?

 

[cyrilbrd]

how do you know if a date is M/DD/YY or D/MM/YY, what I mean to say is how do you segregate dates with days below or equal to the 12th?

2/6/12 and 6/2/12 could both be correct and they could both be different and they could both be the same based on what you described.

How would you like to go about these?

 

[port80]

You can find easily by selecting cell. On top text box, it will show accepting format.
See attached screen shot,
http://img40.imageshack.us/img40/3663/dateissue1.jpg

 

[Jonmo1]

Highlight the column
Click Data - Text to Columns
Deliminated - Next
Next
Date - DMY
Finish.

 

[port80]

@jonmo1:
no luck, I followed ur suggested steps, but seeing same issue.

 

[Jonmo1]

Try same steps again, but use MDY instead of DMY

 

[port80]

I just tried both MDY, DMY again, but no luck
I am using office 2010

 

[pkrishna]

I just tried both MDY, DMY again, but no luck
I am using office 2010

Excuse me for interjecting. BTW, I am using Office 2007

I typed in numbers 41060 thru 41065 in a column.
In the next column, I chose custom format "mm/dd/yy" for the first three rows, and custom format "dd/mm/yy" for the next three rows.
In the column after that, I chose the Excel standard date format option "dd-mmm-yy"
I copied the six numbers from the first column, into the corresponding rows in the next two columns.

Here is what I see.

<TABLE style="WIDTH: 147pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=196 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=64 height=17>41060</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=64>05/31/12</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 51pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=68>31-May-12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>41061</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>06/01/12</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>1-Jun-12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>41062</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>06/02/12</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>2-Jun-12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>41063</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>03/06/12</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>3-Jun-12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>41064</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>04/06/12</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>4-Jun-12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>41065</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>05/06/12</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>5-Jun-12</TD></TR></TBODY></TABLE>

This may not directly answer your question, but might clarify things. I hope.