# Convert 2 digit year range to 4 year range

#### nurgemedia

##### New Member
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
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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### nurgemedia

##### New Member
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
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
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

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

Replies
3
Views
175
Replies
2
Views
293
Replies
4
Views
286
Replies
1
Views
167
Replies
0
Views
129

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,782
Messages
5,766,433
Members
425,354
Latest member
kshivanand21

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

### Which adblocker are you using?

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

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