m/yy or m/yyyy date format

[Bigfoot17]

I am having problems with some date ofrmat, date entry.
I have a column in which I want to enter stricktly the month and year (NO
day of the month).

If I enter 3/11 to me it means March 2011, but to Excel it is March 11, 2008
(or Mar-08, depending on the format). Any suggestions. I have others that
will be doing the data entry, and I do not want to train them to 'fudge' to
get the format correct.

 

[Bigfoot17]

I should add that I will be oding some searches and formula calculations on
the month/year.

 

[RagDyeR]

Train them to use 4 digits for the year!
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

I am having problems with some date ofrmat, date entry.
I have a column in which I want to enter stricktly the month and year (NO
day of the month).

If I enter 3/11 to me it means March 2011, but to Excel it is March 11, 2008
(or Mar-08, depending on the format). Any suggestions. I have others that
will be doing the data entry, and I do not want to train them to 'fudge' to
get the format correct.

 

[Huber57]

Bigfoot17,

If you enter 3/11 in cell A1 it will return 11-Mar. From there, go to
Format | Cells (or hit Control+1). Now you can modify how the date looks.
It will still retain the day, month and year, but will only show what you
want it to.

In the "Number" Tab, select either Date or Custom in the Category window.
Scroll in the type window for how you want it to appear.

For Mar-08, select mmm-yy.
for March 2008, type in Mmmm yyyy

Hope that helps.

 

[Mike H.]

I couldn't figure out a format that would work, but this would. Format all
the cells as text. Then when the user enters the date as 3/11, it displays
3/11 in the cell. Then you could, after the fact, create a formula that
would make it into a date:
If cells with value is in E10:

=DATE(RIGHT(E10,2)+100,IF(MID(E10,3,1)="/",LEFT(E10,2),"0" &LEFT(E10,1)),1)

 

[Dave Peterson]

You can fight excel as much as you like, but you're not going to win.

You can use
3/2011
And excel will see it as March 1, 2011.

 

[smartgal]

I had a similar problem in that I was comparing information for a vlookup and
even though I changed how it displayed, when you clicked in the cell it STILL
showed a full date, meaning I couldn't compare the info - it truly wasn't the
same. Is there not a way to format it to show AND recognize only a month and
year without forcing it to give an actual calendar date such as March 1, 2011?

 

[Gord Dibben]

Not if you want a real date.

A real date must have a day, month, year


Gord Dibben MS Excel MVP

 

[Dave Peterson]

And if you don't want excel to convert your entry to a real date, you could
either:

Preformat the cell as Text, then do the data entry

or

Prefix your entry with an apostrophe: '03/11

Both of these will mean that your entry is text--not a date.

 

[Bigfoot17]

And you cannot do calculations off of this because it is text.
$P$2:$P$400<TODAY()

 

[John C]

The display can be however you want it, the concern is ensuring the date will
always match, if you have someone always enter the year as a 4 digit number,
as previously suggested, this can be achieved. In addition, you can use data
validation to help you. What I suggest:

Select the first cell you will expect entry on.
Go to Data-->Validation.
Settings: Allow should be: CUSTOM. Formula should be: =DAY(A2)=1, where A2
is the initial cell you are setting up. This will 'force' the entry person to
have the day being entered as a 1 (which is the default if they enter in
format of m/yyyy).

In addition, you can use the Input Message tab to instruct the entry person
of the proper format for entry.
Title could be: Enter Date. Input message could be: Enter in format of m/yyyy

just my 2 cents