You are here:

Excel/Macro to reformat Date

Advertisement


Question
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!

Answer
Jason,

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

41365.3333333333

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

Sub abc()
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"
End Sub



--
Regards,
Tom Ogilvy

About Excel
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.