Dates

G

Guest

Guest
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!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
 
Upvote 0
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?
 
Upvote 0
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,<font size=+20><font color="red">Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-03-14 15:13
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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,<font size=+20><font color="red">Nate<font color="blue">O</font></font></font>
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?
 
Upvote 0
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! :biggrin: 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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-03-14 15:52
 
Upvote 0
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, <font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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