Converting Year/Month/Week/Day/Hour to Days

[megbennett]

Hello, I cannot figure out the formula(s) to convert a single cell that contains

'1 Year 8 Months 1 Week 5 Days 17 Hours 30 Minutes' into number of days (would like to round up to a full day, and my need would be based on a 365 day year.

Any help with the formula would be appreciated!

[#2]

@megbennett You could write a formula that breaks down the text string based on the words year, month, weeks, days, hours and minutes. I would favor splitting the text with the help of Text-to-columns and then use several formulae that calculate days and fractions of days.

Example attached.

[#3]

@megbennett 

It's not possible to produce an exact result, because 8 months can be 242, 243, 244 or 245 days depending on the start date: February to October is 242 days in a non-leap year, and March to November is 245 days.

You could use the following custom VBA function:

Function Convert2Days(s As String) As Long
    Dim a() As String
    a = Split(s)
    Convert2Days = Application.RoundUp(365 * a(0) + 365 / 12 * a(2) + 7 * a(4) + a(6) + a(8) / 24 + a(10) / 1440, 0)
End Function

With a string such as 1 Year 8 Months 1 Week 5 Days 17 Hours 30 Minutes in for example D2, the approximate number of days is

=Convert2Days(D2)

This can be filled down if required.

[#4]

@megbennett Check out the solution here Convert datetime text to days (including decimals) - Microsoft Community