Excel date formats
 

I want to enter my date like this: 010105 abd have it format it like this:
January 1, 2005. Help!
--
CLG

Take a look at

http://cpearson.com/excel/datetimeentry.htm

In article ,
"Legal Learning" wrote:

I want to enter my date like this: 010105 abd have it format it like this:
January 1, 2005. Help!

Hi
you'll need VBA for this. You may try the following addin to just achieve
this:
http://www.xldynamic.com/source/xld.QDEDownload.html

--
Regards
Frank Kabel
Frankfurt, Germany
"Legal Learning" schrieb im
Newsbeitrag ...
I want to enter my date like this: 010105 abd have it format it like this:
January 1, 2005. Help!
--
CLG

i'm afraid you might have a slight problem.
In order for xl to recognize a date, it must be in a
format that it can recognize as a date.
with the cell formated at date, xl recognizes 101005 as
the date number and formats it to aug 31, 1927.

-----Original Message-----
I want to enter my date like this: 010105 abd have it
format it like this:
January 1, 2005. Help!
--
CLG
.

CLG

This can be done in Excel. It is a little 'messy' but here we go.

Enter your dates in column A. Just type them as numbers no matter how they
appear. For example,

010105 will appear as 10105
260105 will appear as 260105

Now in Cell B1 cut and paste the following formula:

=VALUE(IF(LEN(A1)=5,"0"&LEFT(A1,1)&"/"&MID(A1,2,2)&"/"&RIGHT(A1,2),LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2)))

This will turn your cell values into a number format that Excel recognises
as a date.

Now highlight all the cells in column B and right click the mouse and
select...

<Format Cells... and select <Custom in the 'category' box.

In the space under 'Type:' add the following....

mmmm d, yyyy

This will convert all your dates to how you want them.

Now select column B and select <copy. Then select cell A1 and select <Paste
special and select <Values.

Now column A will have your dates just as you like them.

This process is not ideal when working with dates but it saves using VBA!

If you have any problems then please do not hesitate to contact me. I will
endeavour to assist further.

Regards


Alex




"Legal Learning" wrote:

I want to enter my date like this: 010105 abd have it format it like this:
January 1, 2005. Help!
--
CLG

Frank Kabel

Check out my post. I found a way of solving the date problem in Excel. See
what you think. It is a bit clumsy but as a one-off it works ok.

Alex

"Frank Kabel" wrote:

Hi
you'll need VBA for this. You may try the following addin to just achieve
this:
http://www.xldynamic.com/source/xld.QDEDownload.html

--
Regards
Frank Kabel
Frankfurt, Germany
"Legal Learning" schrieb im
Newsbeitrag ...
I want to enter my date like this: 010105 abd have it format it like this:
January 1, 2005. Help!
--
CLG

CLG

I forgot to add...once you have typed the formula I gave into cell B1 you
can just drag the formula down to work out all the other dates you need to
convert...

Alex

"Legal Learning" wrote:

I want to enter my date like this: 010105 abd have it format it like this:
January 1, 2005. Help!
--
CLG

Hi Alex
though this works this has one drawback: It's not in the same cell :-)
If this is o.k. for the OP then he may use a formula solution 8less
overhead).

BTW a shorter formula to achieve this would be:
=--TEXT(A1,"00-00-00")

--
Regards
Frank Kabel
Frankfurt, Germany
"Alex" schrieb im Newsbeitrag
...
Frank Kabel

Check out my post. I found a way of solving the date problem in Excel. See
what you think. It is a bit clumsy but as a one-off it works ok.

Alex

"Frank Kabel" wrote:

Hi
you'll need VBA for this. You may try the following addin to just achieve
this:
http://www.xldynamic.com/source/xld.QDEDownload.html

--
Regards
Frank Kabel
Frankfurt, Germany
"Legal Learning" schrieb im
Newsbeitrag ...
I want to enter my date like this: 010105 abd have it format it like
this:
January 1, 2005. Help!
--
CLG