Date Formatting

[reh37]

I want to enter a numeric date (61592) and have Excel convert it to (06/15/92). I've selected the cells and used the Format/Cells and Date selection, however, when I enter the numeric date, Excel converts it to some new date and present it with in the mm/dd/yy format. How can I turn off this conversion, or enter the numeric data so I can do the formatting?

 

[Mark W.]

> With the two digit year representation,
> caution...

I am not a proponent of m/d/yy format. I remember Y2K.

> It is interesting that conversion of text
> strings using your formula works with the
> '-' character but not with the '/'
> character, although generally Excel will
> accept date with either of the two
> characters. Any toughts on that!

Slashes are only valid formatting characters with date formatting characters (e.g., m, dd, yyyy, etc.). To convince yourself of this just try to apply the custom format, 00/00/00, to a 5 or 6-digit integer. Finally, don't confuse date formatting with date entry. When you enter 4/15/2002 or 4-15-2002 Excel is interpeting your input as a date and automatically converting the entered value into a serial date value. This same conversion occurs with =TEXT(A1,"0-00-00")+0; however, its the "+0" that coercing the hyphenated value into a serial value -- not the TEXT function istself.
This message was edited by Mark W. on 2002-05-12 12:53

 

[Yogi Anand]

Hi Mark:
I understand the part regarding Date entry versus true Date value in Excel. Also, no problem regarding the coercion of the the converted text string into a numeric value.

What I am curious about is why would Excel accept the '-' character and not the '/' -- and as you pointed out, it is the same in Custom formatting numbers as well.

I surmise that '/' has some special features, even though both characters also happen to be arithmetic operators as well. I also recognize that because both happen to be arithmetic operators has nothing to do with this issue.

Regards!

 

[Mark W.]

On 2002-05-12 13:10, Yogi Anand wrote:

What I am curious about is why would Excel accept the '-' character and not the '/' -- and as you pointed out, it is the same in Custom formatting numbers as well.

No, a slash isn't the same as a hyphen in custom formats. Look at the error message when you attempt to apply the custom format 00/00/00 to a 5-digit integer. Unlike with date formatting characters slashes are not a valid (built-in) formatting character for use with numeric formatting characters such as 0 and #.
This message was edited by Mark W. on 2002-05-12 13:21

 

[Yogi Anand]

Thanks Mark!

What I am curious about is why would Excel accept the '-' character and not the '/' -- and as you pointed out, it is the same in Custom formatting numbers as well.

Sorry about the ambiguity in my statement -- what I mean is just as the '-' and '/' don't behave the same way in your formula, they don't behave the same way in the Custom Number Formating either.

My apologies for any confusion.

Regards!
This message was edited by Yogi Anand on 2002-05-12 17:43

 

[reh37]

Thanks to all that have provided a solution for me. However, I'm afraid I must admit that I can't get any of the formulas to work. Perhaps I'm entering them incorrectly. Do I first select the cell(s) and then Format, Cells, Date and then enter the formula? I did just that with the latest solution and Excel returned an error stating something like the formula could not be handled. This was true with the other solution using TEXT. Since you've both managed to make this work, I'd appreciate your sending me detailed steps so that I can make this work. Thanks for your patience.

 

[reh37]

Thanks to all that have provided a solution for me. However, I'm afraid I must admit that I can't get any of the formulas to work. Perhaps I'm entering them incorrectly. Do I first select the cell(s) and then Format, Cells, Date and then enter the formula? I did just that with the latest solution and Excel returned an error stating something like the formula could not be handled. This was true with the other solution using TEXT. Since you've both managed to make this work, I'd appreciate your sending me detailed steps so that I can make this work. Thanks for your patience.

 

[Mark W.]

Enter =TEXT(A1,"0-00-00")+0 where cell A1 contains a 5 or 6-digit integer representation of a date (e.g., 61592).

Format the cell containing this formula as...

mm/dd/yy

 

[Boxer]

Hi

I have exactly the opposite problem, I am using a combobox within a form to selct from a list of dates however after I have made the selection the number appears in number format not the date format.!!!

I am sure this is somthing really simple but would be grateful if you could help.

 

[reh37]

Mark, please forgive me for not understanding this. Please let me know the exact sequence to enter this data. Do you first select FORMAT, CELLS, and then what is the selection from the format options? Where do you enter the =Text formula? I'm sure that I first must highlight/select the cells to format first. Sorry to be so dense about this, and thanks for hanging in there with me.

 

[Yogi Anand]

On 2002-05-16 14:43, reh37 wrote:
Mark, please forgive me for not understanding this. Please let me know the exact sequence to enter this data. Do you first select FORMAT, CELLS, and then what is the selection from the format options? Where do you enter the =Text formula? I'm sure that I first must highlight/select the cells to format first. Sorry to be so dense about this, and thanks for hanging in there with me.

Hi reh37:
Here is the step by step description.
1. in cell A1 key in your 5 digit number
61592
2. in cell B1 key-in the following
=TEXT(A1,"0-00-00")+0
3. Highlite cell B1 and then
FORMAT|CELL|NUMBER|Date select say 3/4/97
4. Look at cell B1 -- does it show 6/15/92
5. If you do see 6/15/92 ... you got it
6. If you don't see 6/15/92 ... you did not get it
7. If you got it ... CELEBRATE!; if you did not get it go back to step 1

Regards!