• Macro to format Dates

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Macro to format Dates

    Author
    Topic
    WSHowardC
    AskWoody Lounger
    October 4, 2012 at 7:02 am #485670

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

    [TABLE=”class: MsoNormalTable, width: 609″]
    [TR]
    [TD=”width: 809, bgcolor: transparent”] I would like a macro, that will put the dates into the correct format i.e dd/mm/yyyy-see sample data attached

    Your assistance in resolving this is most appreciated





    [/TD]
    [/TR]
    [/TABLE]

    Reply | Quote
    Viewing 3 reply threads
    Author
    Replies
    • WSHowardC
      AskWoody Lounger
      October 12, 2012 at 3:37 pm #1351501

      Hi Robert

      Thanks for the valuable information.

      Regards

      Howard

      Reply | Quote
    • Maudibe
      AskWoody_MVP
      October 20, 2012 at 11:06 pm #1352590

      Just write a custom Format d/m/yyyy then apply new format to all your cells that have a date.

      HTH,
      Maud

      Reply | Quote
    • WSgrugeon
      AskWoody Lounger
      October 24, 2012 at 9:29 pm #1352981

      Try this formula
      =IF(DAY(B2)>12,B2,DATE(YEAR(B2),DAY(B2),MONTH(B2)))
      then format the cell as date

      Reply | Quote
      • browni
        AskWoody MVP
        October 24, 2012 at 10:25 pm #1352983

        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.

        Reply | Quote
        • WSgrugeon
          AskWoody Lounger
          October 24, 2012 at 10:39 pm #1352986

          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.

          Reply | Quote
    • WSHowardC
      AskWoody Lounger
      October 31, 2012 at 12:35 pm #1354026

      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

      Reply | Quote
    Viewing 3 reply threads
    Reply To: Macro to format Dates

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    March 2025
    S M T W T F S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.