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

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!

3 Replies

# Re: Converting Year/Month/Week/Day/Hour to Days

@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.

# Re: Converting Year/Month/Week/Day/Hour to Days

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. # Re: Converting Year/Month/Week/Day/Hour to Days

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