Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions | |||
Date updates from worksheet to chart & changes date to a date series! Help!! | Charts and Charting in Excel |