Excel Macros Date Change

[wesleyw]

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

[pcstru]

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.

[wesleyw]

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?

[wesleyw]

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?

[CESIL]

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.

[pcstru]

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).

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

[wesleyw]

Works great thanks pcstru!

Wes

[CESIL]

@pcstru ... well done, you obviously understood the question better than I did...

[wesleyw]

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