Date Format: Entering Date as 121214 but want excel displays as 12/12/2014

[Warmhearted]

Hello Helpers:

I want to enter a date as 121214 on cell but want excel displays it as 12/12/2014. Please help me how to format it. Thanks.

Brando

 

[ttdk1]

put 121214 in A1

B1: =TEXT(LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2),"MM/DD/YYYY")

 

[Andrew Poulsom]

You may find this helpful:

Date And Time Entry

 

[Warmhearted]

Thank you for your information Andrew! I'll play with them to see what can I get.

 

[Warmhearted]

Thank you ttdk1. However, I got problem when entering 011213 or 051414 for 01/12/13 and 05/14/14 but excel displays 11/21/13 and 51/41/14. As you can see, Jan thru Sep will encounter problem with this formula. Any idea? Thanks.

Brando

 

[ttdk1]

=text(left(a1,len(a1)-4)&"/"&mid(a1,len(a1)-3,2)&"/"&right(a1,2),"mm/dd/yyyy")

 

[mikerickson]

Perhaps

=DATEVALUE(TEXT(A1, "00""/""00""/""00"))

 

[ttdk1]

thanks mike,

=[COLOR=#333333]TEXT(A1, "00""/""00""/""00")[/COLOR]

 

[Warmhearted]

Thank you Mike. It works.

Brando

 

[Warmhearted]

Thank you for your time ttdk1! It works now.

Brando