Dates
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Dates

  1. #1
    Guest

    Default

     
    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!

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    Guest

    Default

    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?

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    Guest

    Default

    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?

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  9. #9
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com