Dates

Dates in excel seem to be a funny thing.
I would like my cell, formatted as a date, to

1) work like say a phone number. When I enter a series of numbers it puts those numbers into the telephone number style
(123)456-7890. How can I enter a series of numbers and have them show up like mm/dd/yy? ie enter 123456 and have them show up as 12/34/56 Is this possible?
How? Help!

2) Can I format a date cell to increase the date by 1 day in that same cell, say by
hitting the "+" sign, or the up arrow or ?
Is this possible? How? Help!

[Mark W.]

You can enter 123456 and then subsequently choose the Data | Text to Columns... menu command and specify Date format at Step 3 of 3 of the wizard.

On 2002-03-14 14:55, Mark W. wrote:
You can enter 123456 and then subsequently choose the Data | Text to Columns... menu command and specify Date format at Step 3 of 3 of the wizard.

OK, but can I somehow format the cell first
put the numbers in and automatically have them appear in the cell the way I want without any further action, again like a phone number?

[NateO]

You could try formating your cells as a custom number, enter the following format in the input section:

00"/"00"/"00

Hope this helps.

Cheers, NateO

[ This Message was edited by: NateO on 2002-03-14 15:13 ]

[Mark W.]

Sorry, can't be done. Enter 123456 and Excel thinks its an integer -- not a date. Enter 12/34/56 and Excel converts it into an internal date value -- but, not really because December doesn't have 34 days.

[NateO]

Right, just a visual trick. Not really a working date. When I enter 12/34/56 xl2000 converts it to a text string....Cheers,

Nate

[ This Message was edited by: NateO on 2002-03-14 15:12 ]

On 2002-03-14 15:09, NateO wrote:
You could try formating your cells as a custom number, enter the following format in the input section:

00"/"00"/"00

Hope this helps.

Cheers, NateO

[ This Message was edited by: NateO on 2002-03-14 15:13 ]

NATE, Yes this works, but as you said not a real date. Any ideas on the increasing date?

[NateO]

Yessum.

Part 1:

Try the following by Mr. Chip Pearson (this guy is good), worked for me. Right click on the worksheet in question, select view code. Paste the following:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim DateStr As String
On Error GoTo EndMacro
If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Formula)
Case 4 ' e.g., 9298 = 2-Sep-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 1) & "/" & Right(.Formula, 2)
Case 5 ' e.g., 11298 = 12-Jan-1998 NOT 2-Nov-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 2) & "/" & Right(.Formula, 2)
Case 6 ' e.g., 090298 = 2-Sep-1998
DateStr = Left(.Formula, 2) & "/" & _
Mid(.Formula, 3, 2) & "/" & Right(.Formula, 2)
Case 7 ' e.g., 1231998 = 23-Jan-1998 NOT 3-Dec-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 2) & "/" & Right(.Formula, 4)
Case 8 ' e.g., 09021998 = 2-Sep-1998
DateStr = Left(.Formula, 2) & "/" & _
Mid(.Formula, 3, 2) & "/" & Right(.Formula, 4)
Case Else
Err.Raise 0
End Select
.Formula = DateValue(DateStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid date."
Application.EnableEvents = True
End Sub

This is a real date! Format your date cells to the format of your desire. Change Range("A1:A10") to the range of your desire. Make sure you enter the dates as month,day, year, e.g., 12601. Should getcha jan 1, 2001.

Hope this helps.

Cheers, NateO

[ This Message was edited by: NateO on 2002-03-14 15:52 ]

[NateO]

Yessum.

Part 2:

Same deal as above, just modify the code to:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim DateStr As String
On Error GoTo EndMacro
If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Formula)
Case 4 ' e.g., 9298 = 2-Sep-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 1) & "/" & Right(.Formula, 2)
Case 5 ' e.g., 11298 = 12-Jan-1998 NOT 2-Nov-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 2) & "/" & Right(.Formula, 2)
Case 6 ' e.g., 090298 = 2-Sep-1998
DateStr = Left(.Formula, 2) & "/" & _
Mid(.Formula, 3, 2) & "/" & Right(.Formula, 2)
Case 7 ' e.g., 1231998 = 23-Jan-1998 NOT 3-Dec-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 2) & "/" & Right(.Formula, 4)
Case 8 ' e.g., 09021998 = 2-Sep-1998
DateStr = Left(.Formula, 2) & "/" & _
Mid(.Formula, 3, 2) & "/" & Right(.Formula, 4)
Case Else
Err.Raise 0
End Select
.Formula = DateValue(DateStr) + 1
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid date."
Application.EnableEvents = True
End Sub


We just changed ".Formula = DateValue(DateStr)" to ".Formula = DateValue(DateStr) + 1" Looks good from here.

Cheers, NateO

[nisht]

Friend,
You can increment your date just by using following code.
First you format your cell to date format.

say

dd/mm/yyyy

Sub incrementone()

Range("a2").Value = Range("a2").Value + 1

End Sub


this a small code which can solve your problem.

nisht
http://www.pexcel.com