Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re arrange Date in Column from yyyymmdd to mmddyyyy

    I have a file with 2000 entries for demographics. Unfortunately the date of birth column shows dates as yyyymmdd instead of mmddyyyy And to make things worse excel is recognizing entries as "general" and not dates, so when I try to use a date formula I get ####### in the field
    Is there a simple date formula to change this or do I have to parse things and then combine columns. This has to work on a 2000 plus rows for the spreadsheet
    Any help appreciated
    Thanks
    JRK
    example 19470302 should read march 2 1947 or 03/02/1947
    Last edited by jrklein; 2015-03-27 at 09:23.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    ..you could use code like this:
    Code:
    Sub convertDates()
    
    Application.ScreenUpdating = False
    
    For Each cell In [d2:d2001] '<< adjust range required
    zYear = Left(cell, 4)
    zMonth = Mid(cell, 5, 2)
    zDay = Right(cell, 2)
    zDate = DateSerial(zYear, zMonth, zDay)
    cell.Value = zDate
    cell.NumberFormat = "dd-mmm-yyyy"   '<< adjust to suit
    Next
    
    End Sub
    ..see attached example file, with 2000 datevalues

    zeddy
    Attached Files Attached Files

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    The non-macro formula would be: =DATE(LEFT(D2,4),MID(D2,5,2),RIGHT(D2,2)) and then fill down and format the date column as you want. In the short macro from Zeddy, you can see that used in the zYear, zMonth and zDay lines.

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Dear Zeddy and Kweaver both of your solutions are phenomenal. Thankyou. I ran both and because some cells within the 5000 Date of birth records have 0 for a number because the date of birth had not been recorded both solutions error out. Is there a way to build in error handling for records that have 0 or are blank in their cells. I would manually enter but it seems that there are approx. 100 records throughout the spreadsheet....
    Thank
    Jrk

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    JR,

    Here's how to fix both:

    Code:
    Sub convertDates()
    
       Application.ScreenUpdating = False
    
       For Each cell In [d2:d2001] '<< adjust range required
           If cell.Value <> "" And cell.Value <> 0 Then
             zYear = Left(cell, 4)
             zMonth = Mid(cell, 5, 2)
             zDay = Right(cell, 2)
             zDate = DateSerial(zYear, zMonth, zDay)
            cell.Value = zDate
            cell.NumberFormat = "dd-mmm-yyyy"   '<< adjust to suit
         End If
    
       Next
    
    End Sub
    Formula: =IFERROR(DATE(LEFT(D2,4),MID(D2,5,2),RIGHT(D2,2)), "")

    HTH
    Last edited by RetiredGeek; 2015-03-27 at 12:01. Reason: Fixed test in code block
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts
    always says false in cell for the formula version , whether dob cell has real value or 0
    I did not check the macro version as I have to plug it into the database sheet and change reference values....

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    JR,

    Seems to work for me am I missing something?
    dateconv.JPG
    HTH
    Last edited by RetiredGeek; 2015-03-27 at 12:49.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    try this..
    Code:
    Sub convertDates()
    
    Application.ScreenUpdating = False
    
    For Each cell In [d2:d2001] '<< adjust range required
    If cell Like "########" Then
    zYear = Left(cell, 4)
    zMonth = Mid(cell, 5, 2)
    zDay = Right(cell, 2)
    zDate = DateSerial(zYear, zMonth, zDay)
    cell.Value = zDate
    cell.NumberFormat = "dd-mmm-yyyy"   '<< adjust to suit
    End If
    Next
    
    End Sub
    This makes sure that only cells that have 8 digits are processed.

    see also attached..

    zeddy
    Attached Files Attached Files

  9. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2015-03-27)

  10. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi I am probably messing it up but I just don't see it..
    Attached Files Attached Files
    Last edited by jrklein; 2015-03-27 at 14:48.

  11. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    Cool!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #11
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi just got the file to upload so you can see it... Thx
    ALSO FAILS WITH DOB IN THE YEAR 1899 HAS TO BE 1900 AND LATER FOR THE MACRO VERSION
    Last edited by jrklein; 2015-03-27 at 14:58.

  13. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    ..you have four formulas in cell [O2], you should only have one!!

    RG wasn't missing anything at all!
    ..but using xxx Like "########" will trap for other invalid cell contents e.g 194712, 1947121326, etc
    see attached file with fix

    zeddy
    Attached Files Attached Files

  14. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    As far as dates prior to 1900, are they still alive?
    Can't you put them onto another tab??

    or amend code to use
    .. Like "19######"
    which would skip these entries like 18991207 etc etc

    zeddy

  15. #14
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Many thanks, just perfect
    phenomenal all works and I changed the 1800 dob pts to 1900 just legacy patients in the database
    Thanks again
    JK

  16. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    JR,

    You can fix the formula version as follows:
    =IF(INT(LEFT(N2,4))<1900,"Prior 1900",IFERROR(DATE(LEFT(N2,4),MID(N2,5,2),RIGHT(N2 ,2)), ""))
    dateconv.JPG
    HTH
    Last edited by RetiredGeek; 2015-03-27 at 18:28.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  17. The Following 2 Users Say Thank You to RetiredGeek For This Useful Post:

    jrklein (2015-03-27),zeddy (2015-03-27)

Page 1 of 3 123 LastLast

Posting Permissions

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