Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    dates-us/european (97+)

    I often copy and paste directly into excel tables which have dates in both MM/dd/yy format or dd/MM/yy
    The dates are not mixed so if I remember to change my region setting each time according to the date format of the table to be copied I can get round the problem.
    See example
    Col A
    12/11/01
    5/12/01
    15/12/01
    13/1/01
    What I want is a plain and simple macro which will change the first the order of the first 2 pairs of numbers in the entire column range
    I tied using functions "left" and "right" but the string length varies 1or 2 digits and gave up
    Thanx
    Smbs

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: dates-us/european (97+)

    I'm not eactly clear on th ewhat the problem is but if the date value are correct then formatting shoul dsolve the problem. Th efollowing macroo will apply a date format to the current selection<pre>Sub FormatDates()
    Dim oCell As Range
    For Each oCell In Selection
    If IsDate(oCell) Then
    oCell.NumberFormat = "mm/dd/yy"
    End If
    Next
    End Sub</pre>

    You can change the formatting picture to suit your requirements.

    Andrew C

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates-us/european (97+)

    Thanx for your reply but I will try and explain what I want
    Col A _____________________________________________Col A replaced should read

    1/5/01---excel sees this as may 1st --- want it as jan 5th___________ 5/1/01
    12/10/01---excel sees this as oct 12--- I want it as dec 10 __________10/12/01

    In other words I want to swop the number or numbers (sometimes two) before and after the first "forward slash"
    the cell format dd/mm/yy shall remain the same
    Thanx hope u now understandSmbs

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: dates-us/european (97+)

    Apply a custom date format in the form dd/mm/yyyy (and variations).
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates-us/european (97+)

    I tried that but it doesnt work
    It changes the format but the date to excel remains the original date not the date I want!!
    The only solution to the best of my humble knowledge is editing the cell contents which I up to now have done by hand but want to do by means of macro ---lots of cell to change!!!
    Thanx
    SMBS

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: dates-us/european (97+)

    I see. When you import/paste the Euro dates, do they come in a mix where Euro 1/5/01 (May 1, 2001) gets set as US January 5, 2001, a.k.a. 01/05/01, but 13/1/01 (Euro January 13, 2001) gets left as a string reading 13/1/01? I'll start working on a macro, but Andrew or someone else will likely beat me to it!
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates-us/european (97+)

    yeh u got the Picture!!!!
    thanx
    Smbs

  8. #8
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates-us/european (97+)

    Thanx I will give it a go and let you know
    Thanx for your trouble
    Smbs

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: dates-us/european (97+)

    OK, there was a critical error and two non-critical errors in the original code post, now edited (I can't highlight them all, because some stuff was taken out!), so take the post as it now stands and test away.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates-us/european (97+)

    nope---- not working 100%
    See attachment column B was original column A is result of your macro
    Thanx Keep the good work up
    Smbs
    Attached Files Attached Files

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: dates-us/european (97+)

    Corrected code as edited in my 100834 post does solve that problem. Throw a wider range of dates at it and stress test it.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates-us/european (97+)

    I understand the problem--- if the format is d/mm/yyyy the result is screwed
    if the format is dd/m/yyyy the result is screwed
    My table does not hve date in format 09/05/2001 but 9/5/2001 for example
    or 10/1/2001 or 9/12/2001 which all generate errors
    Its now 3 in morning in my part of the world -Imoff to catch 40 winks
    will talk to u tomorrow
    thanx again -I learn a lot from all u great guys!! great fun
    Smbs

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: dates-us/european (97+)

    Hope your rest was peaceful. This should be better, though it's no beauty:

    Sub EDatetoUDate()
    Application.ScreenUpdating = False
    Dim rngCell As Range
    Dim strCVal As String
    Dim intSl1 As Integer, intSl2 As Integer
    If vbYes = MsgBox("Are you certain you want to convert these dates" & vbLf _
    & "from European (dd/mm/yyyy) to US (mm/dd/yyy)?", vbYesNo) Then
    For Each rngCell In Selection
    strCVal = rngCell.Value
    intSl1 = InStr(strCVal, "/")
    intSl2 = InStr(1 + intSl1, strCVal, "/")
    rngCell.Value = Mid(strCVal, intSl1 + 1, intSl2 - intSl1 - 1) & "/" _
    & Left(strCVal, intSl1 - 1) & "/" & Right(strCVal, Len(strCVal) - intSl2)
    Next rngCell
    End If
    Application.ScreenUpdating = True
    End Sub

    Keep testing!
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates-us/european (97+)

    Works like a charm ---I am still trying to understand the code --it looked straightforward to code so I tried to code it myself --ofcourse without success!!.
    I will stress test it and let u know if I have problems
    Thanx again
    Smbs

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: dates-us/european (97+)

    Here's a crude start. It's crude in that there's probably a better way to do this, AND in that it blindly reverses the month and day without any "thought":

    [Edit: Code removed; see subsequent post 100866 in this thread

    Can you test it for me and see if there's anything screwy? (The powers that sign my paycheck are demanding my attention just now.)
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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