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!
 

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
Joined
Jan 15, 2007
Messages
24,089
Are there any other dashes in the text? is there any case without a dash (i.e. a single year)
 

nurgemedia

New Member
Joined
Dec 23, 2019
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
  3. Web
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
Joined
Dec 30, 2008
Messages
14,610
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 23, 2019
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
  3. Web

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 15, 2007
Messages
24,089

ADVERTISEMENT

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
Joined
Dec 23, 2019
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
  3. Web
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:LOL:
After an update I see no more errors but I also only see 1 year : 1992 Honda Prelude
 
Learn Excel from Bill Jelen

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

Forum statistics

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

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