Help with Date please
 

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.

Help with Date please
 

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.

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.

Help with Date please
 

Sorry, I mis-read the format

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(.Value, "yy-") & sQtr & Format(.Value,
"-mm-dd")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Atomic" wrote in message
...
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.