Jan 06 2022 10:30 AM
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!
Jan 06 2022 10:59 AM
@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.
Jan 06 2022 11:13 AM
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.
Aug 02 2022 06:15 AM
@megbennett Check out the solution here Convert datetime text to days (including decimals) - Microsoft Community