I formatted the cell to DATE (mm/dd/yy) but everytime I key in 042909, Excel
changes the date that I entered to something like 09/15/2017. It should be
04/29/09 but it displays different date all the time. How can I fix it? I
tried to highlight the cell then right click the highlighted area format
cells click on 'date' in the menu then I choose the format mm/dd/yy BUT it
does not work. It only works if I personally type in the slashes between
month, day and year. I want Excel to enter the slashes automatically for me
just like it does the dashes for social security number. No matter what date
format I selected, the date changes everytime after I go to the next cell
unless I key in the slashes. Any help is greatly appreciated.

Excel doesn't recognize 042909 as a date. It thinks it's a number. Then when
you format the cell as a date, it calculates the 42,909th day since Jan 1,
1900 and displays that date.

Your choices a
1. Enter the date as 04/29/09
2. Convert the number to a date using something like
=date(mod(a1,100),int(a1/10000),mod(int(a1/100),100)))
3. Write a macro to convert the number for you.

Regards,
Fred.

"pcquest" wrote in message
...
I formatted the cell to DATE (mm/dd/yy) but everytime I key in 042909,
Excel
changes the date that I entered to something like 09/15/2017. It should be
04/29/09 but it displays different date all the time. How can I fix it? I
tried to highlight the cell then right click the highlighted area format
cells click on 'date' in the menu then I choose the format mm/dd/yy BUT
it
does not work. It only works if I personally type in the slashes between
month, day and year. I want Excel to enter the slashes automatically for
me
just like it does the dashes for social security number. No matter what
date
format I selected, the date changes everytime after I go to the next cell
unless I key in the slashes. Any help is greatly appreciated.

pcquest wrote:
I formatted the cell to DATE (mm/dd/yy) but everytime I key in 042909, Excel
changes the date that I entered to something like 09/15/2017. It should be
04/29/09 but it displays different date all the time. How can I fix it? I
tried to highlight the cell then right click the highlighted area format
cells click on 'date' in the menu then I choose the format mm/dd/yy BUT it
does not work. It only works if I personally type in the slashes between
month, day and year. I want Excel to enter the slashes automatically for me
just like it does the dashes for social security number. No matter what date
format I selected, the date changes everytime after I go to the next cell
unless I key in the slashes. Any help is greatly appreciated.


You can enter 4/29 or 4-29 and Excel will know that it's 04/29/2009. You
only need the year if it's different than the current year.

Don't sweat the small stuff...

Bill