date formatting won't work Excel 2003

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ninip - 04 Oct 2007 17:26 GMT

Hi everyone
I am fairly new to Excel 2003.
Here is my problem.
I am inputting names and birthdates in my worksheet. I have a column for
each.
I can't seem to get the date column to work.
This is what I want to show 01/22/98
I know about the Cell format date choices.
But nothing I try works.
I format the cell first or I format the cell last it doesn't matter.
Please help for I need this for work.
Thanks in advance.

Reply to this Message

Tyro - 04 Oct 2007 17:37 GMT

I enter 01/22/98 into a cell and Excel shows 1/22/1998 in the formula bar
(that's what's in the cell). I format the cell, Ctrl+1, Number, Date, Type:
03/14/01 and the cell shows: 01/22/98. Is that what you're doing?

Tyro

> Hi everyone
> I am fairly new to Excel 2003.
[quoted text clipped - 8 lines]
> Please help for I need this for work.
> Thanks in advance.

Reply to this Message

Ninip - 04 Oct 2007 17:59 GMT

Thanks for info.
When I type in the date.....do I need to type the /?
Or can I just type 012298
Thanks in advance.

>I enter 01/22/98 into a cell and Excel shows 1/22/1998 in the formula bar
>(that's what's in the cell). I format the cell, Ctrl+1, Number, Date, Type:
[quoted text clipped - 14 lines]
>> Please help for I need this for work.
>> Thanks in advance.

Reply to this Message

Peo Sjoblom - 04 Oct 2007 18:07 GMT

You need the date delimiter to tell Excel it's a date, when you say it
doesn't work what result did you get?

Signature

Regards,

Peo Sjoblom

> Thanks for info.
> When I type in the date.....do I need to type the /?
[quoted text clipped - 19 lines]
>>> Please help for I need this for work.
>>> Thanks in advance.

Reply to this Message

Tyro - 04 Oct 2007 18:28 GMT

Yes, use must use a valid date format to enter the date. 012298 is a number
not a date.

Valid date formats for January 22, 1998 are: 1-22-98, 1-22-1998, 1/22/98,
1/22/1998, 1-22/98, January 22, 1998, 22-Jan-1998 and 1998/1/22
Valid date formats for the current year include the above and: Oct 4,
October 4, 10/4 and 10-4

Tyro

> Thanks for info.
> When I type in the date.....do I need to type the /?
[quoted text clipped - 19 lines]
>>> Please help for I need this for work.
>>> Thanks in advance.

Reply to this Message

Tyro - 04 Oct 2007 18:31 GMT

The formats I posted are for U.S. settings which are mm/dd/yy. Elsewhere
settings might be dd/mm/yy and you'll have to adjust the entries accordingly

> Yes, use must use a valid date format to enter the date. 012298 is a
> number not a date.
[quoted text clipped - 29 lines]
>>>> Please help for I need this for work.
>>>> Thanks in advance.

Reply to this Message

Bernard Liengme - 04 Oct 2007 17:39 GMT

Start with a new workbook to do this test
Do not format any cells
Enter in A1: 1/22/98
What do you see in the cell?
If you tell us the result we may be able to help

I note that you have CA in your email address; are Regional Settings set to
Canada?
If so, the default date format is dd/mm/yyyy
You may reply here or to my private email
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> Hi everyone
> I am fairly new to Excel 2003.
[quoted text clipped - 8 lines]
> Please help for I need this for work.
> Thanks in advance.

Reply to this Message

Ninip - 04 Oct 2007 18:08 GMT

Hi
I see exactly that.
But when we were trying it at work last night. It kept reversing the day
and month.
Thanks

> Start with a new workbook to do this test
> Do not format any cells
[quoted text clipped - 19 lines]
>> Please help for I need this for work.
>> Thanks in advance.

Reply to this Message

Lars - 06 Oct 2007 00:35 GMT

>Enter in A1: 1/22/98
>
>I note that you have CA in your email address; are Regional
>Settings set to Canada?
>If so, the default date format is dd/mm/yyyy

It is easy enough when you have 22. Excel is not dumb enough to think
that that is a month.

But in my case. When I do as you said, no formatting, and I enter
5/2/98 Excel treats it as "General". Does not take it as a date all.

However if I enter these;

2/5 Excel treats it as February 5
22/5 becomes May 22.
5/2 is February 5
5/22 becomes May 22.

I have an English language Windows, and English Office. But my region
is set to Swedish. In our language when we write 2/5 it actually means
May 2nd.

Some day I hope there will be a separate setting in Excel where I can
fix, either once and for all, or for each workbook, how dates are to
be treated.

I have had Excel since version 4, and I am presently at 2003. They
have all messed with my dates.

Yes, I do know how to format dates. I just wish I did not have to do
it over and over again.

Lars
Stockholm

Reply to this Message

Rate this thread: