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.]

See http://www.mrexcel.com/board/viewtop...c=6233&forum=2.

Keep in mind that Excel has it's own internal date values that are recognized and properly displayed by Excel's built-in date formats. See the Excel Help Index topic for "How Microsoft Excel performs date and time calculations"

[ This Message was edited by: Mark W. on 2002-04-24 16:27 ]

[reh37]

A friend provided a solution to this problem. The solution is: Select column for all cells, or select cell.
Select FORMAT, CELLS, NUMBER, CUSTOM.
In the "type" area key in the following mask:
0#"/"##"/"##
Hit enter to enter this mask.
The cells in the column have now been formatted and the user can enter numeric data that will be automatically formatted with the slashes (mm/dd/yy). No conversion will take place.

[Mark W.]

On 2002-04-25 16:35, reh37 wrote:
A friend provided a solution to this problem. The solution is: Select column for all cells, or select cell.
Select FORMAT, CELLS, NUMBER, CUSTOM.
In the "type" area key in the following mask:
0#"/"##"/"##
Hit enter to enter this mask.
The cells in the column have now been formatted and the user can enter numeric data that will be automatically formatted with the slashes (mm/dd/yy). No conversion will take place.

Yes, but these won't be translated by Excel into internal date values. You won't be able to use them in formulas as though they were dates [e.g., =MONTH(A1)]. I trust your friend explained that to you.

[TiggerToo]

try this:

=(left(a1,1+(len(a1)>5))&"/"&mid(a1,2+(len(a1)>5),2)&"/"&right(a1,2))+0

or

=date(1900+right(a1,2),left(a1,1+(len(a1)>5)),mid(a1,2+(len(a1)>5),2))

I got this from someplace I cannot remember right now, but i hope this help.

Walter

Never stop learning

[Mark W.]

On 2002-04-26 11:30, TiggerToo wrote:
try this:

=(left(a1,1+(len(a1)>5))&"/"&mid(a1,2+(len(a1)>5),2)&"/"&right(a1,2))+0

or

=date(1900+right(a1,2),left(a1,1+(len(a1)>5)),mid(a1,2+(len(a1)>5),2))

I got this from someplace I cannot remember right now, but i hope this help.

Walter

Never stop learning

An awful lot of typing when...

=TEXT(A1,"0-00-0000")+0

...will suffice!

[ This Message was edited by: Mark W. on 2002-04-26 12:33 ]

[reh37]

I tried to use your simplified formatting formula, but could not get it to work. Excel returned errors. Thanks for trying to help.

[Yogi Anand]

On 2002-04-25 16:35, reh37 wrote:
A friend provided a solution to this problem. The solution is: Select column for all cells, or select cell.
Select FORMAT, CELLS, NUMBER, CUSTOM.
In the "type" area key in the following mask:
0#"/"##"/"##
Hit enter to enter this mask.
The cells in the column have now been formatted and the user can enter numeric data that will be automatically formatted with the slashes (mm/dd/yy). No conversion will take place.

Like Mark said, just because you can make it look like a date, it does not mean it will be a Date to Excel for calculations, sorting etc. Mark has provided a very elegant solution for converting the text string to Date.
To make a string of text into dates, you can also use the date function which takes the year, month, and day as arguments.
So say I have a text string '06152002 in cell A1, where 06 represents month, 15 represents the day, and 2002 represents the year, then

=DATE(right(A1,4),left(a1,2),mid(A1,3,2))

will truly convert that text string into DATE for Excel.

_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 17:16 ]

[Mark W.]

On 2002-05-12 10:37, reh37 wrote:
I tried to use your simplified formatting formula, but could not get it to work. Excel returned errors. Thanks for trying to help.

If you're trying to convert a date represented as a 5 or 6-digit integer, modify my suggested formula as shown below...

=TEXT(A1,"0-00-00")+0


[ This Message was edited by: Mark W. on 2002-05-12 12:12 ]

[Yogi Anand]

On 2002-05-12 12:11, Mark W. wrote:

If you're trying to convert a date represented as a 5 or 6-digit integer, modify my suggested formula as shown below...

=TEXT(A1,"0-00-00")+0


[ This Message was edited by: Mark W. on 2002-05-12 12:12 ]

Hi Mark:
With the two digit year representation, caution regarding the cutoff year of 29 still applies (year numbers 29 or less will be in the 2000 series, and 30 or above will be considered in the 1900 series).

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!

Regards