Weird date formatting

[Guest]

I am brand new to Excel and am having trouble formatting a date. We have
different nationalities in the office where each person enters the date
differently:
* month, day, year
* year, month, day
* day month year

Everyone defaults to their own country's way of doing things and we are
getting very confused! To eliminate confusion, for example, I've formatted a
cell "mmm-yy". But if someone enters "11-06" (for November 2006) the value
becomes "Jun-06". Entering "9-07" (September 2007), it becomes Jul-06". If I
enter "2007-8" it remains unchanged. The only time it works is if you
actually enter the short-form month and year (Feb-07), which is kinda missing
the point of formatting the cell to begin with. What is the best way around
this?

 

[Bob Phillips]

Get them to all format as dd-mmm-yyyy

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Now, why didn't I think of that... The point of my post is that people are
people, creatures of habit, and do not enter dates consistently. This is the
reason programmers, etc. develop and use masks, automatic formatting,
combo/list boxes, etc. to help ensure the integrity of data.

 

[Niek Otten]

You won't get there with just formatting.
Probably best is to use a form and echo the full date (02 August, 2002) and ask for confirmation.
I know people have their own way of filling in dates, if they feel it doesn't matter. But I bet in your country too, you are not
invited to enter license plate numbers, DOBs, passport numbers etc in any sequence you like.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Now, why didn't I think of that... The point of my post is that people are
| people, creatures of habit, and do not enter dates consistently. This is the
| reason programmers, etc. develop and use masks, automatic formatting,
| combo/list boxes, etc. to help ensure the integrity of data.
|
| "Christine" wrote:
|
| > I am brand new to Excel and am having trouble formatting a date. We have
| > different nationalities in the office where each person enters the date
| > differently:
| > * month, day, year
| > * year, month, day
| > * day month year
| >
| > Everyone defaults to their own country's way of doing things and we are
| > getting very confused! To eliminate confusion, for example, I've formatted a
| > cell "mmm-yy". But if someone enters "11-06" (for November 2006) the value
| > becomes "Jun-06". Entering "9-07" (September 2007), it becomes Jul-06". If I
| > enter "2007-8" it remains unchanged. The only time it works is if you
| > actually enter the short-form month and year (Feb-07), which is kinda missing
| > the point of formatting the cell to begin with. What is the best way around
| > this?

 

[gls858]

I am brand new to Excel and am having trouble formatting a date. We have
different nationalities in the office where each person enters the date
differently:
* month, day, year
* year, month, day
* day month year

Everyone defaults to their own country's way of doing things and we are
getting very confused! To eliminate confusion, for example, I've formatted a
cell "mmm-yy". But if someone enters "11-06" (for November 2006) the value
becomes "Jun-06". Entering "9-07" (September 2007), it becomes Jul-06". If I
enter "2007-8" it remains unchanged. The only time it works is if you
actually enter the short-form month and year (Feb-07), which is kinda missing
the point of formatting the cell to begin with. What is the best way around
this?

One possibility would be to provide a calendar to select from such
as the one described here:

http://www.ozgrid.com/VBA/excel-calendar-dates.htm

gls858

 

[Guest]

I would try applying a Louisville Slugger against the temple of the defective
keyboard operator! Sounds like time for boss to be boss, thats why He/She
gets the big bucks.
Lou