Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,424
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Macro to format Dates

    I have the dates that when imported, puts it into formatmm/dd/yyyy,where the month is less than 13

    Attached Files Attached Files

  2. #2
    New Lounger
    Join Date
    Sep 2011
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I suspect you are importing a CSV file. When you do that, Microsoft automatically converts dates. And of course, only the US date system exists in the world of Microsoft......

    Try this: change the file type to PRN then open the file. This will open the file without the auto conversion. You can then use the data to columns feature and specify the date column is DMY.

    Robert Flanagan
    Last edited by Medico; 2012-10-09 at 17:01.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,424
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi Robert

    Thanks for the valuable information.

    Regards

    Howard

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Just write a custom Format d/m/yyyy then apply new format to all your cells that have a date.

    HTH,
    Maud

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post
    Try this formula
    =IF(DAY(B2)>12,B2,DATE(YEAR(B2),DAY(B2),MONTH(B2)) )
    then format the cell as date
    David Grugeon
    Brisbane Australia

  6. #6
    WS Lounge VIP Browni's Avatar
    Join Date
    Dec 2009
    Location
    Rochdale, UK
    Posts
    1,656
    Thanks
    38
    Thanked 161 Times in 139 Posts
    Quote Originally Posted by grugeon View Post
    Try this formula
    =IF(DAY(B2)>12,B2,DATE(YEAR(B2),DAY(B2),MONTH(B2)) )
    then format the cell as date
    That won't work.

    Consider dates such as 12/11/2012 & 11/12/2012.

    Presumably Howard has this sorted though as there have been no further replies from him.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Browni View Post
    That won't work.

    Consider dates such as 12/11/2012 & 11/12/2012.

    Presumably Howard has this sorted though as there have been no further replies from him.
    Quite right I should have said IF(DAY(B2)>13,B2,DATE(YEAR(B2),DAY(B2),MONTH(B2)) )

    Unless the OP tells us that he has solved it we have no way of knowing. He may be embarrassed to say that none of the suggestions made are appropriate.

    Anyhow, Howard, even if you have solved it a different way, it could be helpful to others to know how you did it.
    David Grugeon
    Brisbane Australia

  8. The Following User Says Thank You to grugeon For This Useful Post:

    HowardC (2012-10-31)

  9. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,424
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi David

    Thanks for the help, much appreciated. I managed to solve the problem by using text to column and setting the date to mdy and it worked for me.

    Regards

    Howard

Posting Permissions

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