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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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
Back
Top