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

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### mikerickson

##### MrExcel MVP
Are there any other dashes in the text? is there any case without a dash (i.e. a single year)

#### nurgemedia

##### New Member
Are there any other dashes in the text? is there any case without a dash (i.e. a single year)

Hello Mike, thank you for your interest!
In some cases there might be additional dashes for example : 04-08 Acura TL/TL Type-S or 90-05 Mazda Miata / MX-5
In a case where there is only a single year it is already in 4 digit format like 1974 Datsun

#### jasonb75

##### Well-known Member
Edit: - slight change to formula to allow for additional example,
Cell Formulas
RangeFormula
B2:B4B2{=IF(ISNUMBER(--LEFT(A2,4)),A2,TEXTJOIN("-",1,ROW(INDIRECT(YEAR(DATEVALUE("1/1/"&LEFT(A2,2)))&":"&IFERROR(YEAR(DATEVALUE("1/1/"&MID(A2,4,2))),YEAR(TODAY())))))&REPLACE(A2,1,FIND(" ",A2)-1,""))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

#### nurgemedia

##### New Member

Edit: - slight change to formula to allow for additional example,
Cell Formulas
RangeFormula
B2:B4B2{=IF(ISNUMBER(--LEFT(A2,4)),A2,TEXTJOIN("-",1,ROW(INDIRECT(YEAR(DATEVALUE("1/1/"&LEFT(A2,2)))&":"&IFERROR(YEAR(DATEVALUE("1/1/"&MID(A2,4,2))),YEAR(TODAY())))))&REPLACE(A2,1,FIND(" ",A2)-1,""))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Hello Jason.

Thank you very much for taking the time to make this example but it is not working for me.

I have tried hand typing the formula into my sheet, making a new sheet and hand typing there, copying your example and pasting it into a new sheet.
All I get is #NAME?
I even tried using Google sheets as an alternative and that actually gave me a response of just 1 year + the original name - 1992 Honda Prelude

Is there something that I might be missing?

#### Fluff

##### MrExcel MVP, Moderator
Your profile says that you are using Xl 2019, is that the case? TextJoin is only available to users of 365 & 2019, so if you are using a different version it would explain the #NAME error.

#### mikerickson

##### MrExcel MVP

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``````

#### nurgemedia

##### New Member
Your profile says that you are using Xl 2019, is that the case? TextJoin is only available to users of 365 & 2019, so if you are using a different version it would explain the #NAME error.

Thank you for your input Fluff!

Turns out yes I was out of date
After an update I see no more errors but I also only see 1 year : 1992 Honda Prelude

#### jasonb75

##### Well-known Member
After an update I see no more errors but I also only see 1 year : 1992 Honda Prelude
Sounds like you didn't confirm the array with Ctrl Shift Enter.

#### nurgemedia

##### New Member
Sounds like you didn't confirm the array with Ctrl Shift Enter.

When I use ctrl+shift+enter it just inserts the formula into the cell and there is no result.

Replies
3
Views
174
Replies
2
Views
291
Replies
4
Views
286
Replies
1
Views
167
Replies
0
Views
127

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,700
Messages
5,766,007
Members
425,322
Latest member
galaxy6623top

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

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