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?
16th July 2012, 04:27 PM #1
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?
16th July 2012, 04:40 PM #2
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.
16th July 2012, 04:43 PM #3
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?
16th July 2012, 04:49 PM #4
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?
16th July 2012, 07:00 PM #5
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.
17th July 2012, 08:00 AM #6
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).
Set sRange = Selection
For Each cell In sRange
cell.Value = Replace(cell.Text, ".", "/")
cell.Value = DateValue(cell.Value)
cell.NumberFormat = "mm.dd.yyyy"
17th July 2012, 08:41 AM #7
Works great thanks pcstru!
17th July 2012, 04:19 PM #8
@pcstru ... well done, you obviously understood the question better than I did...
17th July 2012, 09:40 PM #9
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
By ahuxham in forum Office Software
Last Post: 18th December 2009, 10:27 AM
By sLiDeR in forum Windows
Last Post: 24th May 2008, 06:35 PM
By oyoung in forum Windows
Last Post: 30th March 2007, 10:27 AM
By wesleyw in forum How do you do....it?
Last Post: 20th December 2006, 10:01 PM
By steve in forum Educational Software
Last Post: 16th May 2006, 02:49 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)