Convert 2 digit year range to 4 year range

[nurgemedia]

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]

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!

 

[nurgemedia]

I think this UDF will do what you want

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]

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

Book1
	A	B	C	D	E
1	Vehicle	Manufactured on	Disincorporated on	Years worked	Notes
2	92-01 Honda Prelude	1992	2001	9	1992-1993-1994-1995-1996-1997-1998-1999-2000-2001 Honda Prelude
3	14- Infiniti Q50 AWD W/O DDS	2014	2019	5	2014-2015-2016-2017-2018-2019 Infiniti Q50 AWD W/O DDS
4	1974 Datsun	Special	Special		1974 Datsun
5	14-16 Opel Astra 2.0 AWD	2014	2016	2	2014-2015-2016 Opel Astra 2.0 AWD
6	1975 Corvette	Special	Special		1975 Corvette
nurgemedia

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]

@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.