Basic--How do I make dates look a certain way in a cell?

[Guest]

Here's what I'd like to do: I'd like to type 41507 in a cell, and have it
display as "4/15/07" or "04/17/07". Instead, it pops up some crazy date like
7/31/85.

I've figured out why this is (the number indicates which day since January
1, 1900, and inserts that date). But I can't figure out if it is possible to
do what I want. Is there a technique for typing in a date without slash marks
or periods, and have these appear in the cell automatically? Thanks for any
help you can give.

ddc

 

[Guest]

This will *look* like the date you want to display, but it certainly won't BE
that date:

Select the cell(s) to be impacted

From the Excel main menu:
<format><cells><number tab>
Categor: Custom
Type: 00"/"00"/"00
Click the [OK] button

So...if you type 41507 in a cell
it will display as 04/15/07
(but its value will STILL be 41507)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Paul B]

Dee, have a look here for some code that should do what you want

http://www.cpearson.com/excel/DateTimeEntry.htm

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Niek Otten]

http://xldynamic.com/source/xld.QDEDownload.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Here's what I'd like to do: I'd like to type 41507 in a cell, and have it
| display as "4/15/07" or "04/17/07". Instead, it pops up some crazy date like
| 7/31/85.
|
| I've figured out why this is (the number indicates which day since January
| 1, 1900, and inserts that date). But I can't figure out if it is possible to
| do what I want. Is there a technique for typing in a date without slash marks
| or periods, and have these appear in the cell automatically? Thanks for any
| help you can give.
|
| ddc

 

[Guest]

Dee, have a look here for some code that should do what you want

http://www.cpearson.com/excel/DateTimeEntry.htm

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Guest]

Thanks to all who replied; it solved my problem and taught me a little bit
more about Excel at the same time. Paul, I think your stuff was a little more
advanced than I can handle, but I've bookmarked your site for future use as
it has lots of of information .

ddc

 

[*alan*]

http://xldynamic.com/source/xld.QDEDownload.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

Niek ----
In the example shown on that link, it demonstrates input of 112 being
changed to
indicate 11/02/2004 (11th Feb 2004 UK style). Issues of the year entry
aside, how did the QDED decide that 112 shouldn't have been changed to
01/12/2004 (12th Dec UK style)? If the user had wanted to express that date
would he have had to input 0112? Just curious . . .

 

[*alan*]

Niek ----
In the example shown on that link, it demonstrates input of 112 being
changed to
indicate 11/02/2004 (11th Feb 2004 UK style). Issues of the year entry
aside, how did the QDED decide that 112 shouldn't have been changed to
01/12/2004 (12th Dec UK style)? If the user had wanted to express that
date would he have had to input 0112? Just curious . . .

*** (I meant 1st Dec 2004)

 

[Niek Otten]

In that same article, it says:

<the 112 is converted to 11th Feb 2004 as the DatePriority setting is set to Day>

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| | > http://xldynamic.com/source/xld.QDEDownload.html
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
|
| Niek ----
| In the example shown on that link, it demonstrates input of 112 being
| changed to
| indicate 11/02/2004 (11th Feb 2004 UK style). Issues of the year entry
| aside, how did the QDED decide that 112 shouldn't have been changed to
| 01/12/2004 (12th Dec UK style)? If the user had wanted to express that date
| would he have had to input 0112? Just curious . . .
| --
| Alan
|
| >
| > | > | Here's what I'd like to do: I'd like to type 41507 in a cell, and have
| > it
| > | display as "4/15/07" or "04/17/07". Instead, it pops up some crazy date
| > like
| > | 7/31/85.
| > |
| > | I've figured out why this is (the number indicates which day since
| > January
| > | 1, 1900, and inserts that date). But I can't figure out if it is
| > possible to
| > | do what I want. Is there a technique for typing in a date without slash
| > marks
| > | or periods, and have these appear in the cell automatically? Thanks for
| > any
| > | help you can give.
| > |
| > | ddc
| >
| >
|