FARMAT DATE

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,824
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jan 14, 2003
Messages
1,824
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I want 2006, but can I change it as I want, year and range, on the code.
I will appreciate it.

thanks
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

Code:
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

Well-known Member
Joined
Jan 14, 2003
Messages
1,824
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks Hotpepper.
sorry it is late.
 

Forum statistics

Threads
1,141,740
Messages
5,708,237
Members
421,553
Latest member
Geeyj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top