how can I stop Excel Advancing the year by 1 when I enter a date?
 

I did something which caused a problem with entering dates in Excel. When I
enter a date, it looks fine until I leave the cell. Then the year advances by
one. For example if I type in 05/05, when I tab out the cell displays May-06.
I have tried right clicking on the cell and selecting the mmmyy selection,
but it makes no difference. I also selecting from tried the format dropdown
menu. Nothing changed it. the only way I could enter a date and have it stay
as entered was to put a quote mark before the numbers. what did I do to cause
this problem and how can i fix it?

how can I stop Excel Advancing the year by 1 when I enter a date?
 

Hi Priscilla

If you not enter the year Excel use th current year

Enter 05/05/05 for the date May-05-05 and format it as mmmyy and you get May-05



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Priscilla" wrote in message ...
I did something which caused a problem with entering dates in Excel. When I
enter a date, it looks fine until I leave the cell. Then the year advances by
one. For example if I type in 05/05, when I tab out the cell displays May-06.
I have tried right clicking on the cell and selecting the mmmyy selection,
but it makes no difference. I also selecting from tried the format dropdown
menu. Nothing changed it. the only way I could enter a date and have it stay
as entered was to put a quote mark before the numbers. what did I do to cause
this problem and how can i fix it?

how can I stop Excel Advancing the year by 1 when I enter a date?
 

What you enter is not May 2005, it is May 5th current year, so if you want
to enter 05/05 without excel using current year is to use a text
representation, precede the entry with an apostrophe ' and enter 'May-05

The reason? Excel uses numbers for dates, 1 is one day and today() Apr 1st
2006 is 38,808 days since Jan 0 1900 (Excel year zero), that means Excel
need a date to be entered with date delimiters (dependent on the regional
settings of the computer) and it needs year month and day and when you enter
05/05 and omits one necessary ingredient Excel guesses that it is the year
and uses current year so what you see when you format it as mmm-yy is excel
formatting May 5th 2006 and obviously that would be May-06. So you either
have to enter text or enter for instance 05/01/05 and format as mmm-yy and
it will display May-05

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Priscilla" wrote in message
...
I did something which caused a problem with entering dates in Excel. When I
enter a date, it looks fine until I leave the cell. Then the year advances
by
one. For example if I type in 05/05, when I tab out the cell displays
May-06.
I have tried right clicking on the cell and selecting the mmmyy selection,
but it makes no difference. I also selecting from tried the format
dropdown
menu. Nothing changed it. the only way I could enter a date and have it
stay
as entered was to put a quote mark before the numbers. what did I do to
cause
this problem and how can i fix it?

how can I stop Excel Advancing the year by 1 when I enter a date?
 

Hi Priscilla:

Both Ron and Peo have indicated the correct method of entering dates.
However if you have already entered dates using the single quote you can
convert them with:

=DATE(2000+RIGHT(A1,2),--LEFT(A1,2),1)
--
Gary's Student


"Priscilla" wrote:

I did something which caused a problem with entering dates in Excel. When I
enter a date, it looks fine until I leave the cell. Then the year advances by
one. For example if I type in 05/05, when I tab out the cell displays May-06.
I have tried right clicking on the cell and selecting the mmmyy selection,
but it makes no difference. I also selecting from tried the format dropdown
menu. Nothing changed it. the only way I could enter a date and have it stay
as entered was to put a quote mark before the numbers. what did I do to cause
this problem and how can i fix it?

how can I stop Excel Advancing the year by 1 when I enter a date?
 

I'm putting my music database in excel I came across a track called
December 1943 what i did is December (1943) in brackets.
Priscilla wrote:
I did something which caused a problem with entering dates in Excel. When I
enter a date, it looks fine until I leave the cell. Then the year advances by
one. For example if I type in 05/05, when I tab out the cell displays May-06.
I have tried right clicking on the cell and selecting the mmmyy selection,
but it makes no difference. I also selecting from tried the format dropdown
menu. Nothing changed it. the only way I could enter a date and have it stay
as entered was to put a quote mark before the numbers. what did I do to cause
this problem and how can i fix it?