Convert 2 digit year range to 4 year range

nurgemedia

New Member
Joined
Dec 23, 2019
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
  3. Web
Hello!

I've been tasked with converting a year range from a 2 digit range and 4 digits with all the years between the start and end listed out separated by dashes.
I understand the process to do the conversion of 2 digits to 4 but there is a few additional steps involved that I have not been able to figure out so perhaps an example of what I'm trying to do would be the best way to explain this.

Example : Year Start - Year End
The cell information : 92-01 Honda Prelude
The result needs to be formatted like this : 1992-1993-1994-1995-1996-1997-1998-1999-2000-2001 Honda Prelude


Each cell will have different ranges and then there will be cases where the range will state just 1 year on, for example


Example : Year Start - Year Current
The cell information : 14- Infiniti Q50 AWD W/O DDS
The result needs to be formatted like this : 2014-2015-2016-2017-2018-2019 Infiniti Q50 AWD W/O DDS

Any insights would be greatly appreciated!
 

nurgemedia

New Member
Joined
Dec 23, 2019
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
  3. Web
When I use ctrl+shift+enter it just inserts the formula into the cell and there is no result.

AH HA! I got it. It looks like the curly braces disappear in the formula bar. when I hit the ctrl+shift+enter it then inserts the braces but if I add them it inserts the formula.

Thank you so much Jason!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

nurgemedia

New Member
Joined
Dec 23, 2019
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
  3. Web
I think this UDF will do what you want
VBA Code:
Function ExpandDate(aString As String) As String
    Dim workString As String, startNum As Long, endNum As Long, Suffix As String
    workString = LCase(aString)
    workString = Replace(workString, " ", vbNullString)
    If workString Like "#-*" Then
        workString = "0" & workString
    End If
    If workString Like "##-*" Then
        startNum = Format(DateValue("1/1/" & Left(workString, 2)), "yyyy")
    End If
    endNum = Year(Date)
    If workString Like "##-##*" Then
        endNum = Format(DateValue("1/1/" & Mid(workString, 4, 2)), "yyyy")
    End If
    If endNum < startNum Then
        workString = CStr(startNum)
        startNum = endNum
        endNum = Val(workString)
    End If
   
    Do
        ExpandDate = ExpandDate & "-" & startNum
        startNum = startNum + 1
    Loop Until startNum > endNum
    ExpandDate = Mid(ExpandDate, 2)
   
    Suffix = aString
    Do Until Not (Mid(Suffix, 1, 1) Like "#" Or Mid(Suffix, 1, 1) = "-" Or Mid(Suffix, 1, 1) = " ")
        Suffix = Mid(Suffix, 2)
    Loop
    ExpandDate = ExpandDate & " " & Application.Trim(Suffix)
End Function

Thank you for taking the time to write out this VBA logic Mike!
I have no clue how to implement it into Excel but the logic is very helpful for writing some Javascirpt 😁
 

hernantorres23

Active Member
Joined
Nov 21, 2019
Messages
269
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Web
Thank you for taking the time to write out this VBA logic Mike!
I have no clue how to implement it into Excel but the logic is very helpful for writing some Javascirpt 😁

I'm late, but here there is other option. If you write it in JavaScript, please share it, with us :cool:
Book1
ABCDE
1VehicleManufactured onDisincorporated onYears workedNotes
292-01 Honda Prelude1992200191992-1993-1994-1995-1996-1997-1998-1999-2000-2001 Honda Prelude
314- Infiniti Q50 AWD W/O DDS2014201952014-2015-2016-2017-2018-2019 Infiniti Q50 AWD W/O DDS
41974 DatsunSpecialSpecial1974 Datsun
514-16 Opel Astra 2.0 AWD2014201622014-2015-2016 Opel Astra 2.0 AWD
61975 CorvetteSpecialSpecial1975 Corvette
nurgemedia


VBA Code:
Option Explicit
'by Hernan Torres (Mikel ERP)
'December 23, 2019
'Refer to Sheet1
'Question by nurgemedia
'topic: https://www.mrexcel.com/board/threads/convert-2-digit-year-range-to-4-year-range.1118721/

Sub checkFleet()
Dim a, b, c
Dim m() As Variant
Dim i, j, k, l, z As Long
Dim testCell As Integer
Dim strResult As String

On Error GoTo ctrl_error
Application.ScreenUpdating = False

m = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
For i = 1 To UBound(m)
j = i + 1
If IsEmpty(m(i, 1)) Then GoTo siga
testCell = CInt(Replace(Left(m(i, 1), 4), "-", ""))
If testCell > 1900 Then
testCell = True
Else: testCell = False
End If
Select Case testCell
Case True
Cells(j, 1).Offset(0, 4).Value = m(i, 1)
Cells(j, 1).Offset(0, 1).Value = "Special"
Cells(j, 1).Offset(0, 2).Value = "Special"
Case Else
a = Year(DateValue("1-1-" & Left(m(i, 1), 2)))
Cells(j, 1).Offset(0, 1).Value = a
If IsNumeric(Mid(m(i, 1), Application.WorksheetFunction.Find("-", m(i, 1)) + 1, 2)) Then
b = Year(DateValue("1-1-" & Mid(m(i, 1), Application.WorksheetFunction.Find("-", m(i, 1)) + 1, 2)))
l = Mid(m(i, 1), 7, Len(m(i, 1)) - 6)
Else
b = Year(Date)
l = Mid(m(i, 1), 5, Len(m(i, 1)) - 4)
End If
Cells(j, 1).Offset(0, 2).Value = b
Cells(j, 1).Offset(0, 3).Value = b - a
c = b - a + 1
For z = 1 To c
strResult = strResult & "-" & a
a = a + 1
Next z
k = Len(strResult)
strResult = Mid(strResult, 2, k - 1) & " " & l
Cells(j, 1).Offset(0, 4).Value = strResult
strResult = Empty
End Select
siga:
Next i

ctrl_error:
Select Case Err.Number
Case 0
Application.ScreenUpdating = True
If testCell = 0 Then
MsgBox "Please check source of data, range A2 is empty", vbInformation, "Mikel ERP by @hernantorres23"
Range("A2").Activate
Exit Sub
End If
Range("F1").Activate
Application.ScreenUpdating = True
MsgBox "Job done!!! ...analyzed data", vbInformation, "Mikel ERP by @hernantorres23"
Case Else
Application.ScreenUpdating = True
MsgBox "Please check source of data, range A2 is empty!!!" & Chr(13) & _
       "Error number " & Err.Number & " " & Err.Description, vbCritical, "Mikel ERP by @hernantorres23"
Range("A2").Activate
End Select

End Sub

Here the full example
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,468
Office Version
  1. 365
Platform
  1. Windows
@hernantorres23

A copule of observations from a quick glance at your code. While how you've written your code is not explicitly wrong, it does go against what many would consider as best practice.

I suggest reviewing the way in which you declare your variables, you have 7 Long / Integer varaibles that you have declared as variant. Actually, that might be 6 Long / Integer and 1 String, using a,b,c etc as variables instead of meaningful keywords makes your code difficult for others to decipher. Not helpful for anyone who might need to make future changes to it.
Additionally, there is no reason to use Integer (unless you're working in 16 bit), most people use Long by default now.

Also, while it is quite common for people to add comments to code saying who it was written by, what it relates to, link to the thread, etc. Nobody (except you) wants to see your name every time a message box pops up.
 

Forum statistics

Threads
1,147,497
Messages
5,741,499
Members
423,662
Latest member
Ajmal Khursand

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