Date formating

[ylittlejohn]

If I type a date 32702 in the cell, how can I get it to display the correct date of 3-27-02. In other words I would like to not have to type the dash. Is it possible? Thanks

 

[BabyTiger]

Hi,

You can try select Format|Cells from menu bar, and then select custom, and in the type field, insert:

m-dd-yy

HTH

 

[ylittlejohn]

Thanks but when I enter 32702 that gives me the date of 7-13-89. What have I done wrong?
This message was edited by ylittlejohn on 2002-03-27 16:15

 

[IML]

On 2002-03-27 16:13, ylittlejohn wrote:
Thanks but when I enter 22702 that gives me the date of 7-13-89. What have I done wrong?

You could use a formula
=DATE(2000+RIGHT(A1,2),LEFT(A1,1+(LEN(A1)>5)),MID(A1,2+(LEN(A1)>5),2))

assuming all your dates in 2000.

This will conver your numbers into dates.
Excel treats time as fractions of day. 1 = 1/1/1900 to most excel users. So today is 37,342 and 32,702 is some previous date


Better option: Text to columns.

Highlight row - data - text to columns - next, next again, click date MDY.

For text to column or formula to work you must enter a two digit day.
This message was edited by IML on 2002-03-27 16:22

 

[Yogi Anand]

On 2002-03-27 16:17, IML wrote:

On 2002-03-27 16:13, ylittlejohn wrote:
Thanks but when I enter 22702 that gives me the date of 7-13-89. What have I done wrong?

You could use a formula
=DATE(2000+RIGHT(A1,2),LEFT(A1,1+(LEN(A1)>5)),MID(A1,2+(LEN(A1)>5),2))

assuming all your dates in 2000.

This will conver your numbers into dates.
Excel treats time as fractions of day. 1 = 1/1/1900 to most excel users. So today is 37,342 and 32,702 is some previous date


Better option: Text to columns.

Highlight row - data - text to columns - next, next again, click date MDY.

For text to column or formula to work you must enter a two digit day.
This message was edited by IML on 2002-03-27 16:22

Hi IML:
I liked your clear and detailed explanation.
I just wanted to add to your statement
For text to column or formula to work you must enter a two digit day
and the year must be in two digits and two digits only!


_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature
This message was edited by Yogi Anand on 2003-01-19 14:24

 

[IML]

Yogi,
I didn't believe you on the text to columns thing until I tried it - good to know.

What came to mind when reading this was a past formula I picked up somewhere (read pilfered).
If you use
=(LEFT(A1,1+(LEN(A1)>5))&"/"&MID(A1,2+(LEN(A1)>5),2)&"/"&RIGHT(A1,2))+0

excel will figure out the 1900 or 2000 issue based on its own logic.
This message was edited by IML on 2002-03-27 20:14

 

[Yogi Anand]

Hi IML:
I hope you agree that this interaction between cyber-buddies makes us all a little richer!

 

[nisht]

Make a custom format..

First select date and then custom

Now in type give value

dd mm yy

or

dd mm yyyy

ni****h desai

http://www.pexcel.com

 

[Yogi Anand]

On 2002-03-27 21:16, nisht wrote:
Make a custom format..

First select date and then custom

Now in type give value

dd mm yy

or

dd mm yyyy

ni****h desai

http://www.pexcel.com

Hi Ni****h:
Just Custom formatting does not cut it. If you see the earlier posting in this thread Baby Tiger proposed this first and yLittleJohn posted back that it did not work.
Merely Custom formatting 32702 as mm dd yy ended up in resulting date being 07/13/89 -- What yLittleJohn wants is to have 32702 converted into 3/27/02.

By the way, I have looked at your web site -- I am impressed!

 

[Dave Hawley]

Hi LittleJohn

This is possible via VBA but would entail you always adding the date as 6 characters. For your example you would need to type 32702 as 032702 If this is ok you can use this code in the Worksheet module. This code will act only on the range A1:A10, to place in the code right click on the sheet name tab, select "View Code" and paste it in.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim strDate As String

If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
strDate = Target
Application.EnableEvents = False
On Error Resume Next
Target = DateValue(Left(strDate, 2) & "-" & Mid(strDate, 3, 2) & "-" & Right(strDate, 2)) * 1
Target.NumberFormat = "mm-dd-yy"
Application.EnableEvents = True
On Error GoTo 0
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
If Not IsDate(Target) Then Target.NumberFormat = "@"
End If
End Sub