Excel Date Format

J

Julie

I am creating an Excel spreadsheet that goes into next
year. I have set the date format as: Mar-04. Every time
I enter 05, it automatically changes to 04. However, if
I enter only the first two letters of the month (Ma-05),
it takes it. This occurs even when I continue to
increase the margin. Can anyone help me, please?
Thanks,
Julie
 
R

Ron Rosenfeld

I am creating an Excel spreadsheet that goes into next
year. I have set the date format as: Mar-04. Every time
I enter 05, it automatically changes to 04. However, if
I enter only the first two letters of the month (Ma-05),
it takes it. This occurs even when I continue to
increase the margin. Can anyone help me, please?
Thanks,
Julie
Click to expand...

I suspect you are not telling us everything. But perhaps this discussion will
help.

The format you set is for display purposes only. It has no effect on how Excel
parses your data entry.

I suspect that you are using a Mac or have set the 1904 date system. When you
enter a number like 05 in a cell that is formatted as date, Excel does not
perform any conversions. So '5' is the fifth day with Jan 1, 1904 being day 0.
The date being stored is 6 Jan 1904 and, with your format, gets displayed as
Jan-04 (January 1904).

Excel stores dates as serial numbers.

Look in the formula bar and see what date is there.

In entering dates, you need to either be specific, or understand all the rules
that apply.

But without using VBA, you can't enter a one or two digit number and have it
interpreted as a date in this century.

You need to use a separator, (determined by your regional settings), and then
Excel will interpret the entry as a date. What date it thinks you entered
depends on how unambiguous your entry is and also what your regional settings
are.

In general, if a year is not specified, it will default to the current year.
If a two digit year is entered, how they get interpreted depends on the
regional settings (and that also depends on the version of Windows you are
running).


--ron
 
H

hcj

Hi Julie,
You might have noticed that when you do enter mar-04,
the date that is loading is mar 4,2004, etc. I think
this because Excel is taking mar-04 as 3/4/ and supplying
the current year by default. When you enter mar-05 it's
taking it as 3/5/ and supplying 2004.
One method to try - enter the first instance explicitly
as 3/1/04, then drag down to get months in sequence into
the next year. This works dandy if you need to increment
one month per row.
If you need multiple instances of a month over several
rows, do a copy and paste over the rows, then drag into
the next row for the next month, etc.
OR, simply type in what you need explicitly. A bit
busier, but not much. The format controls the display.

Hope this helps
 
J

jeff

Hi, Julie,

Excel doesn't care that you've formatted your cells
as Mar-04; when you enter 05-05, wanting May-2005, it
thinks you're entering May 5th, 2004 (present year).
It wants a full date to distinguish; it doesn't know you
want to enter month-year only (in spite of your format).
I suppose there's more demand for month&day entries than
month-year, so that's the default read.

Check your Ma-05 entry; is it truly a date? or just
text "Ma-05"?

jeff
 
R

Ron Rosenfeld

I suspect you are not telling us everything. But perhaps this discussion will
help.

The format you set is for display purposes only. It has no effect on how Excel
parses your data entry.

I suspect that you are using a Mac or have set the 1904 date system. When you
enter a number like 05 in a cell that is formatted as date, Excel does not
perform any conversions. So '5' is the fifth day with Jan 1, 1904 being day 0.
The date being stored is 6 Jan 1904 and, with your format, gets displayed as
Jan-04 (January 1904).

Excel stores dates as serial numbers.

Look in the formula bar and see what date is there.

In entering dates, you need to either be specific, or understand all the rules
that apply.

But without using VBA, you can't enter a one or two digit number and have it
interpreted as a date in this century.

You need to use a separator, (determined by your regional settings), and then
Excel will interpret the entry as a date. What date it thinks you entered
depends on how unambiguous your entry is and also what your regional settings
are.

In general, if a year is not specified, it will default to the current year.
If a two digit year is entered, how they get interpreted depends on the
regional settings (and that also depends on the version of Windows you are
running).


--ron
Click to expand...

Reading some other postings who assumed,unlike me, that you entered Mar-05, I
would agree with them that, in accordance with what I've written for the logic
of entering dates in Excel, it is getting translated to 5 Mar 2004.

That would happen regardless of use of Mac/Windows 1904/1900 date systems.


--ron
 
Top