FARMAT DATE

[srizki]

I would like to format a cell in a way so that I only have to put number of a month, from 1 - 12 and it should convert it to month and year. For example if I put 9 it should convert it to September 2006.

I am using this '=DATE(2006,B3,30) formula, but I have to use another cell to put number of the month.

Thanks

 

[Scott Huish]

It can't be done with cell formatting, but it can be done with VBA. How do you want to determine what year it should be? Always the current year or what? And in what range do you want this to happen?

 

[srizki]

I want 2006, but can I change it as I want, year and range, on the code.
I will appreciate it.

thanks

 

[Scott Huish]

The code could figure out what the current year was, but since you want it hardcoded, here you go, this is currently set for Column A.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim yr As Integer, r As Range, c As Range
yr = 2006
Set r = Intersect(Target, Range("A:A"))
If r Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each c In r
        If IsNumeric(c) Or IsDate(c) Then
            c = Int(c)
            c = DateSerial(yr, c, 1)
            c.NumberFormat = "mmmm yyyy"
        End If
    Next
Application.EnableEvents = True
End Sub

 

[srizki]

Thanks Hotpepper.
sorry it is late.