Formula to convert hours to days

Question

name    day1 day2  day3   day4 day5  totalhr  DAY 
person1 2hr  4hr   1.5hr  1hr  2hr   11.5 
Person2 3hr  2.4hr 2hr    2hr  3hr   12.5 
Person3 3hr  2hr   2hr    2hr  3hr   12hr 

I have an Excel sheet that has text values (like the examples above).

I want a way I can convert them into days such that:

If I have 12hrs, it rounds off to the nearest day and becomes 1 Day. If it is more than 12 hrs, it can say for example 1 day 2 hrs. Until the 2hrs grows to 12hrs it becomes 1 day.

It is important to note that the values are in General format and not date or time.

I see the hours is making it so difficult.

Can we ignore the hrs from both the input value: it should be total 12 = 1day 3hr (where 3 is the hours or without hr).

I don't want to convert to strings. If the formula is without hrs it is fine. My point is how to had i.e. 2 , 3 , 1, each representing hours when it gets to a total of 12, it becomes one day. 12 hours = 1 day. you can ignore hrs in the formula.

Thanks

Answer 1

How about:

=IF(MOD(SUBSTITUTE(SUBSTITUTE(E5,"hrs",""),"hr",""),24)>=12,ROUND(SUBSTITUTE(SUBSTITUTE(E5,"hrs",""),"hr","")/24, 0), FLOOR(SUBSTITUTE(SUBSTITUTE(E5,"hrs",""),"hr","")/24,1)&" D "&MOD(SUBSTITUTE(SUBSTITUTE(E5,"hrs",""),"hr",""),24)&" H")

Where E5is your cell for example.

Explanation:

SUBSTITUTE(SUBSTITUTE(E5,"hrs",""),"hr","")

removes "hrs" and "hr".

Then other part checks if the MOD of hours is >= 12:

• When Yes, then round it to the next day
• When No, then write down the FLOOR, followed by the hours

Answer 2

You can use this function to get the numbers from the cell with time (if the time is in cell A2):

=LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))))

You can add the value of several cells by having this formula in a SUM()function, one for each cell.

Say that you put that in E5you can then convert it to days and hours with something like :

= QUOTIENT(E5,24) & IF(E5/24<2, " Day ", " Days ") & MOD(E5,24) & IF(MOD(E5,24)<2, " Hour", " Hours")

And round up anything less than a day by putting that in an if like:

= IF(E5/24<1, "1 Day",QUOTIENT(E5,24) & IF(E5/24<2, " Day ", " Days ") & REST(E5,24) & IF(REST(E5,24)<2, " Hour", " Hours"))

Sources: https://www.ablebits.com/office-addins-blog/2017/11/22/excel-extract-number-from-string/

Answer 3

How it works:

• I'm assuming that Data in Range A2:F4.
• Array (CSE) Formula in G2:

{=SUM(--SUBSTITUTE(A2:F2,"hr",""))&"hr"}

N.B. Finish Formula with Ctrl+Shift+Enter & fill down.

• Enter this Formula in H2 & fill it down.

  =INT(INT(SUBSTITUTE(G2,"hr","")/24)) & " days" & " "&INT(MOD(SUBSTITUTE(G2,"hr",""),24)) & " hours"
  

Adjust Cell references in the Formula as needed.