Date format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cathy C - 23 Jan 2008 03:00 GMT

I want to format a column of cells so that when 012208 is entered with the
numeric keypad, 01/22/2008 appears in the cell. So far, I'm getting
6/3/1933.

Thanks,
Cathy

Reply to this Message

JP - 23 Jan 2008 03:44 GMT

Hate to say it but why not just type the extra two characters to make
it 1/22/08?

If you type "12208" in a cell, Excel assumes you are entering a serial
date (hence the conversion to 6/3/1933). How much easier to just type
the two "/" and don't force Excel to guess what you want.

HTH,
JP

> I want to format a column of cells so that when 012208 is entered with the
> numeric keypad, 01/22/2008 appears in the cell. So far, I'm getting
> 6/3/1933.
>
> Thanks,
> Cathy

Reply to this Message

Bob Phillips - 23 Jan 2008 08:51 GMT

This is because of the way that Excel stores dates. I would suggest VBA,
http://www.cpearson.com/excel/DateTimeEntry.htm

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I want to format a column of cells so that when 012208 is entered with the
>numeric keypad, 01/22/2008 appears in the cell. So far, I'm getting
>6/3/1933.
>
> Thanks,
> Cathy

Reply to this Message

Cathy C - 23 Jan 2008 17:53 GMT

Thank's to 'Gary's Student'! The helper column works great! Just what I was
looking for.

Subject: RE: Date format 1/23/2008 2:11 AM PST

By: Gary's Student
In: microsoft.public.excel.worksheet.functions

There are two ways:

1. a helper column
2. VBA macro

If you are entering data in column A, then in B1 enter:

=DATE(2000+RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2)) and copy down

The VBA method does the same thing, but in place.

Signature

Gary's Student - gsnu200765

> This is because of the way that Excel stores dates. I would suggest VBA,
> http://www.cpearson.com/excel/DateTimeEntry.htm
[quoted text clipped - 5 lines]
>> Thanks,
>> Cathy

Reply to this Message