Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert date format (2003)

    I have a csv file that I am trying to convert the date format. I have the following macro that doesn't seem to work. Can someone take a look and see how to change this macro to convert the date from 2002-09-01 12:00AM to a normal date format without the time.

    Sub ChangeFormat3()

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert date format (2003)

    I think the problem is that Excel doesn't recognize 2002-09-01 12:00AM as a date/time value, so formatting won't have any effect. Try replacing

    ActiveCell = Format(ActiveCell.Value, "dd/mm/yy")

    with

    ActiveCell = Left(ActiveCell, 10)

    and if necessary, add

    ActiveCell.NumberFormat = "dd/mm/yy"

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert date format (2003)

    When I run the macro I get runtime error "6" Overflow.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert date format (2003)

    We'd have to see the workbook.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert date format (2003)

    I think I fixed it - at least the macro works. I changed the data type "interger" to "long" in the following:

    Dim RowCount As Integer (changed to Long)
    Dim List As Integer (changed to Long)

    The macro works.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert date format (2003)

    Ah yes, the Integer data type has a mximum of 32,767, but an Excel worksheet has 65,536 rows.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert date format (2003)

    The conversion is converting dates like 2006-07-31 12:00AM to 31/07/2006. Is there anyway to correct this? Thanks for any help you can provide.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert date format (2003)

    If you don't want 2006-07-31 12:00AM to be converted to 31/07/2006, what would you want it to be converted to instead?

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert date format (2003)

    07/31/2007. The other dates like 1/1/2007 are converting correctly. It just seems to be on days like 30 or 31 that convert to the day first and then the month and then the year. I want the month first and then the day and then the year.....

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert date format (2003)

    In your first post, you specified dd/mm/yy as format - could that be the cause?

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert date format (2003)

    Hmm - dumb me. Of course 1/1/2007 is going to look correct. Do I just change the format to mm/dd/yyyy?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert date format (2003)

    Yep. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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