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.
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"
MsgBox "There was an error processing:" & vbCrLf _
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.