Excel formula


Need help to build a formula to take the following text output to convert and add years and weeks to the days so that the output just shows days, hours and minutes:

output inserted into excel column:    4 years, 24 weeks, 1 day, 14 hours, 55 minutes
need converted to just days, hours, minutes:   1629 days, 14 hours, 55minutes




I would suguest using a concatenate & sum based formula, for example:

"=CONCATENATE(SUM(B1*365,B2*7,B3)," Days, ",B4," Hours ",B5," Min's")"

See attached file


If you have to get this from a cell that has the data in that exact format stored as text then I suggest that you do it this way:

  1. Split the data up using Text-to-Columns (Data tab > Text to Columns).
    Split the data on the comma.
  2. Then, use this formula to get the number of days:
    =(LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1)*365+IF(MOD(LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1),4)=0,(LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1))/4,0))+(LEFT(TRIM(B1),FIND(" ",TRIM(B1))-1)*7)+LEFT(TRIM(C1),FIND(" ",TRIM(C1))-1)

    This is a big formula and it assumes that you have the data starting in column A row 1.
    This will get the days and then you just use regular concatenation to get the other data.

Including the concatenation of the hours and minutes, the final formula, following the above example, could look like this:

=(LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1)*365+IF(MOD(LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1),4)=0,(LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1))/4,0))+(LEFT(TRIM(B1),FIND(" ",TRIM(B1))-1)*7)+LEFT(TRIM(C1),FIND(" ",TRIM(C1))-1)&" days, "&LEFT(TRIM(D1),FIND(" ",TRIM(D1))-1)&" hours, "&LEFT(TRIM(E1),FIND(" ",TRIM(E1))-1)&" minutes"

Also, the above formulas should account for leap years by adding 1 day for every 4 years in the 'years part' of the data.


Answer the Question

You must create an account to use the forum. Create an Account or Login