date formatting

J

Jayme

I want to enter only the month and the year in a cell: for example,
i'll key in 08/01 (August 2001) and i want to format the cell to
display Aug-01. Is there a way to do this?

Thanks,

Jayme
 
E

ElsiePOA

To the best of my knowledge, you can not enter just a month and year i
Excel because Excel uses serial numbers for each day and doesn'
account for months except by knowing the serial number of the day i
question.

You could enter "8/1/1" and format it to appear as Aug-01 using th
"format, date" menu.

The actual date however will still be 8/1/1.

Or if you just want Aug-01 as text rather than an actual date which yo
can manipulate, you can just type 'Aug-01.

Obviously, using this method you would not be able to sort as dates
they would sort alphabetically
 
G

Guest

Format the cell for a date, and if it doesn't have that
particular style you can customize it to be your style.
 
R

Ron Rosenfeld

I want to enter only the month and the year in a cell: for example,
i'll key in 08/01 (August 2001) and i want to format the cell to
display Aug-01. Is there a way to do this?

Thanks,

Jayme
Click to expand...

If you could use different columns for data entry vs display, you could use the
formula:

=DATE(1900+100*(MOD(A1,100)<30)+MOD(A1,100),INT(A1/100),1)

to convert your entry.

Otherwise, you would need a VBA event driven macro. Something like the
following might work for you. To enter it, right click on the worksheet tab
and select View code from the menu that opens. Then paste the below macro into
the window that opens.

============================
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim AOI As Range
Dim temp As Variant, yr As Integer, mnth As Integer
Const DtFormat As String = "mm-yy"
Application.EnableEvents = False

Set AOI = [A:A]

If Intersect(Target, AOI) Is Nothing Then GoTo DONE
If Target.Count <> 1 Then GoTo DONE

temp = InputBox("Enter Date String", , Format(Target.Value, "mmyy"))

If Len(temp) = 0 Then
Target.Clear
GoTo DONE
End If

yr = temp Mod 100
mnth = Int(temp / 100)

Target.Value = DateSerial(yr, mnth, 1)
Target.NumberFormat = DtFormat
DONE: Application.EnableEvents = True
End Sub
=======================


--ron
 
J

Jayme

The macro you showed works, but doesn't format the cell exactly like I
want. It shows, for example, 02-04. I want it to display Feb '04.
Is this possible?







Ron Rosenfeld said:
I want to enter only the month and the year in a cell: for example,
i'll key in 08/01 (August 2001) and i want to format the cell to
display Aug-01. Is there a way to do this?

Thanks,

Jayme
Click to expand...

If you could use different columns for data entry vs display, you could use the
formula:

=DATE(1900+100*(MOD(A1,100)<30)+MOD(A1,100),INT(A1/100),1)

to convert your entry.

Otherwise, you would need a VBA event driven macro. Something like the
following might work for you. To enter it, right click on the worksheet tab
and select View code from the menu that opens. Then paste the below macro into
the window that opens.

============================
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim AOI As Range
Dim temp As Variant, yr As Integer, mnth As Integer
Const DtFormat As String = "mm-yy"
Application.EnableEvents = False

Set AOI = [A:A]

If Intersect(Target, AOI) Is Nothing Then GoTo DONE
If Target.Count <> 1 Then GoTo DONE

temp = InputBox("Enter Date String", , Format(Target.Value, "mmyy"))

If Len(temp) = 0 Then
Target.Clear
GoTo DONE
End If

yr = temp Mod 100
mnth = Int(temp / 100)

Target.Value = DateSerial(yr, mnth, 1)
Target.NumberFormat = DtFormat
DONE: Application.EnableEvents = True
End Sub
=======================


--ron
Click to expand...
 
J

Jayme

The macro you showed works, but doesn't format the cell exactly like I
want. It shows, for example, 02-04. I want it to display Feb '04.
Is this possible?







Ron Rosenfeld said:
I want to enter only the month and the year in a cell: for example,
i'll key in 08/01 (August 2001) and i want to format the cell to
display Aug-01. Is there a way to do this?

Thanks,

Jayme
Click to expand...

If you could use different columns for data entry vs display, you could use the
formula:

=DATE(1900+100*(MOD(A1,100)<30)+MOD(A1,100),INT(A1/100),1)

to convert your entry.

Otherwise, you would need a VBA event driven macro. Something like the
following might work for you. To enter it, right click on the worksheet tab
and select View code from the menu that opens. Then paste the below macro into
the window that opens.

============================
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim AOI As Range
Dim temp As Variant, yr As Integer, mnth As Integer
Const DtFormat As String = "mm-yy"
Application.EnableEvents = False

Set AOI = [A:A]

If Intersect(Target, AOI) Is Nothing Then GoTo DONE
If Target.Count <> 1 Then GoTo DONE

temp = InputBox("Enter Date String", , Format(Target.Value, "mmyy"))

If Len(temp) = 0 Then
Target.Clear
GoTo DONE
End If

yr = temp Mod 100
mnth = Int(temp / 100)

Target.Value = DateSerial(yr, mnth, 1)
Target.NumberFormat = DtFormat
DONE: Application.EnableEvents = True
End Sub
=======================


--ron
Click to expand...
 
R

Ron Rosenfeld

The macro you showed works, but doesn't format the cell exactly like I
want. It shows, for example, 02-04. I want it to display Feb '04.
Is this possible?
Click to expand...
Sure. You can make the output be whatever date format you like. For the
change you want, merely change the line:

Const DtFormat As String = "mm-yy"

to

Const DtFormat As String = "mmm \'yy"


The codes are the same as you would use in Format/Cells/Number/Custom Type:






--ron
 
Top