Excel/Macro to reformat Date
I have an excel spreadsheet that has a box with the date in it. But it is formatted in this fashion: 01 April, 2013 08:00
I need to get rid of the time and then redo it back into a date format as: April 01, 2013
This way I can resort my list based on the appropriate date format that is acceptable by Excel.
Thanks for your help!
If I enter that date and time in a cell (and make sure it is actually treated/stored as an excel date/time), then if I format the cell as general I see it actually contains
this is called a date serial number. the 41365 represents the number of full days that have elapsed from the base date being used by excel for date recording. In windows, this defaults to the first day in 1900. (as the century changes from 1899 to 1900.). The .333333333 represents 1/3 of a day. 1/3 of a day is 8 hours or 8 AM. (without designating AM or PM, the 8:00 is interpreted as 8 AM)
Note if you formatted you cell as general and it still looked like 01 April, 2013 08:00 then it is being stored as a string literal/text. for me, removing the comma after April and hitting enter cause it to be stored as a date.
so to remove the time, just edit the cell and delete the .33333333333
Now format the cell as you wish. (right click on the cell and choose format=>cells then go to the number tab and choose date. then select the format that you want to use. I would expcet April 01, 2013 to be one of the choices, but if not you can use a custom number format and put in
mmmm dd, yyyy
Hope that is what you are asking.
I just noticed in your subject you said macro. It is better to put the request in the body of the question. I often never look at the subject so if there is unique information there, then I am apt to miss it.
Anyway, I selected the cell with the entry
01 April, 2013 08:00
then ran the macro below. It produced April 01, 2013
Dim r As Range, s As String, dt As Date
Set r = ActiveCell
s = ActiveCell.Text
s = Replace(s, ",", "")
dt = CDate(s)
dt = Fix(dt)
ActiveCell.Value = dt
ActiveCell.NumberFormat = "mmmm dd, yyyy"
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here