date formatting in excel

Microsoft Microsoft office excel 2007 -...
August 27, 2010 at 07:11:41
Specs: Windows 7
In a file we have hundreds of records with date format mm/dd/yy. In order to process we need the format to be mmddyy. Is there any easy way to format the date in this way?

See More: date formatting in excel

Report •

August 27, 2010 at 07:32:08

Select the cells and FormatCells... - select 'Custom' and enter mmddyy in the 'Type:' box. (Note - no quotes around the mmddyy)

If you want a macro to do it try this:

Sub DateFormat()
Dim rngCell As Range

For Each rngCell In ActiveSheet.UsedRange
    If IsDate(rngCell.Value) Then
        rngCell.NumberFormat = "mmddyy"
    End If
Next rngCell


Report •

August 27, 2010 at 10:10:40
when we do that it returns a number, after some research about how excel handles dates, which is the amount of days since Jan 1 1900. We can get it to give us the format we want if in that cell we write =TEXT(#####, "mmddyy") but we can't find a way to do that for every cell...

Report •

August 27, 2010 at 11:25:09

When you say when we do that it returns a number, was this manually changing the cell format or was it running the macro.

As you say, Excel stores dates as numbers (the integer part of the number is the date).
Are you sure that the dates you have, are being recognized as dates by Excel and are not just text strings.

You can test a cell for date vs. text by formatting it as a number - if it displays the same 'date', it is text, but if it changes to a number such as 40000, Excel has recognized it as a date.

If cells are recognized as dates, then the macro should be able to re-format them to mmddyy.
If the cells are text, then we should be able to convert the text to an Excel date and format them as mmddyy using a macro.

The following macro finds cells that contain two "/" characters and are not stored as an Excel date.
It converts the text in the cell to a proper Excel date number and then formats it as mmddyy.

If you try this macro you must test it on a copy of your data as changes made by a macro cannot be undone with the undo button/function.
Ensure that it works as expected - and then make a backup of your data before running it on the real data.

Sub TextToDate()
Dim rngCell As Range
Dim strAddr As String

On Error GoTo ErrHnd

For Each rngCell In ActiveSheet.UsedRange
    'save a cell address to help debug
    strAddr = rngCell.Address
    If InStr(1, rngCell.Formula, "/") > 0 And _
            InStr(InStr(1, rngCell.Formula, "/") + 1, rngCell.Formula, "/") _
            > 0 Then
        'this cell has two "/" characters and will be treated as a date
        'get position of both "/"
        intSlash1 = InStr(1, rngCell.Text, "/")
        intSlash2 = InStr(intSlash1 + 1, rngCell.Text, "/")
        'take text and place the parts mm, dd & yy into DateSerial
        'which needs year, month & day
        'and save the date serial in the cell
        rngCell.Value = DateSerial(Right((rngCell.Text), 2), _
            Left(rngCell.Text, intSlash1 - 1), _
            Mid(rngCell.Text, intSlash1 + 1, intSlash2 - intSlash1 - 1))
        'now format the date as mmddyy
        rngCell.NumberFormat = "mmddyy"
    End If
Next rngCell
Exit Sub

'error handler
MsgBox "There was an error processing:" & vbCrLf _
        & strAddr
End Sub

As I have no idea what other data you have in your worksheet, I have added a message that identifies the cell address if an error occurs. It may help debug the conversion process.


Report •

Related Solutions

August 27, 2010 at 11:42:34

Report •

Ask Question