Date formating

ylittlejohn · Mar 27th, 2002, 05:01 PM

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 · Mar 27th, 2002, 05:06 PM

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 · Mar 27th, 2002, 05:13 PM

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 · Mar 27th, 2002, 05:17 PM

Quote:

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 · Mar 27th, 2002, 05:41 PM

Quote:

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

Quote:

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 · Mar 27th, 2002, 09:13 PM

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 · Mar 27th, 2002, 09:24 PM

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

nisht · Mar 27th, 2002, 10:16 PM

Make a custom format..

First select date and then custom

Now in type give value

dd mm yy

or

dd mm yyyy

nishith desai

http://www.pexcel.com

Yogi Anand · Mar 30th, 2002, 11:51 PM

Quote:

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

nishith desai

http://www.pexcel.com

Hi Nishith:
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 · Mar 31st, 2002, 03:52 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 27th, 2002, 05:01 PM	#1
ylittlejohn New Member Join Date: Mar 2002 Posts: 5	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

Mar 27th, 2002, 05:06 PM	#2
BabyTiger Board Regular Join Date: Mar 2002 Location: Wellington Posts: 104	Hi, You can try select Format\|Cells from menu bar, and then select custom, and in the type field, insert: m-dd-yy HTH

Mar 27th, 2002, 05:13 PM	#3
ylittlejohn New Member Join Date: Mar 2002 Posts: 5	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 ]

Mar 27th, 2002, 09:13 PM	#6
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	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 ]

Mar 27th, 2002, 09:24 PM	#7
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi IML: I hope you agree that this interaction between cyber-buddies makes us all a little richer!

Mar 27th, 2002, 10:16 PM	#8
nisht Board Regular Join Date: Feb 2002 Location: Ahmedabad Gujarat Posts: 303	Make a custom format.. First select date and then custom Now in type give value dd mm yy or dd mm yyyy nishith desai http://www.pexcel.com

Mar 31st, 2002, 03:52 AM	#10
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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