Microsoft Excel - Date to month number - Asked By Dan on 19-Mar-13 06:14 AM

In coulmn C:C I have dates (1/2/2013).  I want to loop thru C:C and change the long date to just the month number.

example: 1/2/2013 change to 1, 2/12/2013 change to 2 and so on.  The code I am using is not doing want I want I cannot figure out why. It cahnges 1/2/2013 to 1/2/2020.

Dim oCell As Range
   
    Range("C2:C1048576").Select
       
      For Each oCell In Selection
     
        If oCell.Value = "" Then GoTo EndRow
     
        oCell.Value = Left(oCell, Len(oCell) - 2)
              
      Next oCell
     
      Set oCell = Nothing
   
EndRow:

Any help would be appreciated.

Dan
Harry Boughen replied to Dan on 19-Mar-13 06:55 AM
Hello Dan
Try this mod.

Sub DateChange()
Dim oCell As Range
   
    Range("C2:C10").Select
     
    For Each oCell In Selection
     
      If oCell.Value = "" Then GoTo EndRow
     
      oCell.Value = Month(oCell.Value)
      oCell.NumberFormat = "00"
        
    Next oCell
     
    Set oCell = Nothing
   
EndRow:
End Sub

Regards
Harry
Dan replied to Harry Boughen on 19-Mar-13 07:45 AM
Thanks! That worked.  I have another problem.  Lets say, in cell A1 I have W486-Drill 

I want cell B1 to show just the W  Any ideas?

Dan

Harry Boughen replied to Dan on 19-Mar-13 04:32 PM
Hi Dan,

=LEFT(A1,1)

Regards
Harry