+ Post New Thread
Results 1 to 9 of 9
How do you do....it? Thread, Excel Macros Date Change in Technical; Can anyone suggest how I would create a macro to change a date of dd.mm.yy or d.m.y to dd.mm.yyyy please? ...
  1. #1
    wesleyw's Avatar
    Join Date
    Dec 2005
    Location
    Kingswinford
    Posts
    2,205
    Thank Post
    223
    Thanked 50 Times in 44 Posts
    Blog Entries
    1
    Rep Power
    30

    Excel Macros Date Change

    Can anyone suggest how I would create a macro to change a date of dd.mm.yy or d.m.y to dd.mm.yyyy please?



    Wes

  2. #2


    Join Date
    May 2009
    Posts
    2,871
    Thank Post
    258
    Thanked 766 Times in 581 Posts
    Rep Power
    269
    You may need to explain a bit more about what you want to do.

    In excel dates are stored as a number representing days past x. So dd.mm.yy is just one way of displaying the date. Given a date, you can use the TEXT function to force into a particular format, i.e. =text(<cell>,"dd.mm.yyyy") but the content of the cell is now treated as text. You could just right click and format it as you want it - I'm not quite sure why you would need to go to a macro.

  3. #3
    wesleyw's Avatar
    Join Date
    Dec 2005
    Location
    Kingswinford
    Posts
    2,205
    Thank Post
    223
    Thanked 50 Times in 44 Posts
    Blog Entries
    1
    Rep Power
    30
    I would like a macro so that I can go to any cell and achieve this usign a shortcut key. Then preferably after conversion chnage the formatting of the cell as well to long date?

  4. #4
    wesleyw's Avatar
    Join Date
    Dec 2005
    Location
    Kingswinford
    Posts
    2,205
    Thank Post
    223
    Thanked 50 Times in 44 Posts
    Blog Entries
    1
    Rep Power
    30
    Also with the cell format set to dd.mm.yyyy it don't change the value 1.1.1 or 01.01.01 is still 1.1.1 or 01.01.01 after using =text(<cell>,"dd.mm.yyyy") in the adjacent cell?

  5. #5

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,403
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    As @pcstru says you need to give more info...

    How are the dates you want to change entered at present...without knowing where you are starting from it is hard to offer any advice.

  6. #6


    Join Date
    May 2009
    Posts
    2,871
    Thank Post
    258
    Thanked 766 Times in 581 Posts
    Rep Power
    269
    The following should work. It will change text (excel will treat 01.01.01 as text, not a date) to a date and then set the format of the cell to display the date as per your spec. You need to highlight the cell (or a range of cells) and then run the macro (assign it to a hotkey first).

    Code:
    Sub fDate()
    
       Set sRange = Selection
       
       For Each cell In sRange
          cell.Value = Replace(cell.Text, ".", "/")
          cell.Value = DateValue(cell.Value)
          cell.NumberFormat = "mm.dd.yyyy"
       Next
       
    End Sub

  7. Thanks to pcstru from:

    wesleyw (17th July 2012)

  8. #7
    wesleyw's Avatar
    Join Date
    Dec 2005
    Location
    Kingswinford
    Posts
    2,205
    Thank Post
    223
    Thanked 50 Times in 44 Posts
    Blog Entries
    1
    Rep Power
    30
    Works great thanks pcstru!

    Wes

  9. #8

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,403
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    @pcstru ... well done, you obviously understood the question better than I did...

  10. #9
    wesleyw's Avatar
    Join Date
    Dec 2005
    Location
    Kingswinford
    Posts
    2,205
    Thank Post
    223
    Thanked 50 Times in 44 Posts
    Blog Entries
    1
    Rep Power
    30
    He has obviously had to deal with the lower level of user that say things like the machine is slow or the thingy has stopped working


    Wes

SHARE:
+ Post New Thread

Similar Threads

  1. [MS Office - XP] Excel Macro - If Cell Value
    By ahuxham in forum Office Software
    Replies: 0
    Last Post: 18th December 2009, 10:27 AM
  2. Win XP Kill off date changed again!!
    By sLiDeR in forum Windows
    Replies: 6
    Last Post: 24th May 2008, 06:35 PM
  3. help with excel macro needed
    By oyoung in forum Windows
    Replies: 0
    Last Post: 30th March 2007, 10:27 AM
  4. Excel Cell colour changes
    By wesleyw in forum How do you do....it?
    Replies: 3
    Last Post: 20th December 2006, 10:01 PM
  5. Excel Macro - Edexcel GCE Exams
    By steve in forum Educational Software
    Replies: 0
    Last Post: 16th May 2006, 02:49 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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