Help with Date please
I made a change in what you gave in but I still get the date and year in
opposit sides:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" '<== change to suit
Dim sQtr As String
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If IsDate(.Value) Then
sQtr = Application.Roman(Int(Month(.Value) + 2) / 3)
.Value = Format(Year(.Value), "-00") & sQtr & Format(.Value,
"-mm-dd")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
This is the result:
2016-I-01-07, 2016-II-04-08, 2016-III-07-07, 2016-IV-10-07
Is there a way to reverse the order of just the date and yearand keep just
the last two digit of the year rather than the entire year.
Thank you.
"Bob Phillips" wrote:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
Dim sQtr As String
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If IsDate(.Value) Then
sQtr = Application.Roman(Int(Month(.Value) + 2) / 3)
.Value = Format(Day(.Value), "00-") & sQtr & Format(.Value,
"-mm-yy")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Atomic" wrote in message
...
Is there a way anyone can help me try and convert the date when I enter it
into something like this below:
07-I-01-01, 07-II-04-01, 07-III-07-01 or 07-IV-10-01
Where the year comes first then the number in which Quarter it belongs to
and then the month broken by the date. The Quarters are devided up as
follows
... 01-Jan-Mar, 02-Apr-Jun, 03-Jul-Sep, 04-Oct-Dec.
Your help is really appreciated and thnak you for help in advance.
|