CODEWITHSUNDEEP
excelvbadatetimeexcel-formula
Adi S
Adi S

Reputation: 1

In Excel, Convert duration in text format to duration (years)

I have a duration value from a Splunk output in "2 year(s), 9 month(s), 16 days(s)" format coming into excel. I need to analyze this. I was trying to use TIMEVALUE() to convert this to number of years. Not having much luck. Dont need the solution but a general pointer would be appreciated

TIMEVALUE() did not work

Upvotes: -3

Views: 55

Answers (2)

P.b
P.b

Reputation: 11628

=SUM(TOROW(--TEXTSPLIT(A1," "),2)/{1,12,360})

You may want to change 360 to 365 or 366. I used 360 like in DATEFRAC:

=LET(n,TOCOL(--TEXTSPLIT(A1," "),2),@n+YEARFRAC(0,EDATE(0,@DROP(n,1))+DROP(n,2)))

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166755

In VBA for example:

Sub Tester()
    Debug.Print GetYears("2 year(s), 9 month(s), 16 days(s)") '2.79383
    Debug.Print GetYears("9 month(s), 16 days(s)")            '0.79383
    Debug.Print GetYears("16 days(s)")                        '0.04383
    Debug.Print GetYears("1 year(s), 16 days(s)")             '1.04383
    
    Debug.Print GetYears("yesterday")                         '0
End Sub

'Parse time period string `s` "[x years(s),] [y month(s),] [z days(s)]"
'  to return the number of years
Function GetYears(s As String) As Double
    Dim arr, i As Long, n As Long, el
    arr = Split(s, ",") 'split on comma
    For Each el In arr  'check eack element of the array
        Select Case True
            Case InStr(1, el, "year", vbTextCompare) > 0
               GetYears = GetYears + Val(el)
            Case InStr(1, el, "month", vbTextCompare) > 0
               GetYears = GetYears + Val(el) / 12
            Case InStr(1, el, "day", vbTextCompare) > 0
               GetYears = GetYears + Val(el) / 365
        End Select
    Next el
End Function

Upvotes: 0

Related Questions