Date formating
Date formating
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Date formating

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

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

    m-dd-yy

    HTH

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

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

    Default

    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

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  10. #10
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

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