Date Formatting

reh37

New Member
Joined
Apr 23, 2002
Messages
7
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?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
I tried to use your simplified formatting formula, but could not get it to work. Excel returned errors. Thanks for trying to help.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
On 2002-05-12 12:11, Mark W. wrote:
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

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
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top