Formula to Convert Hours to Days
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...
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
3 Answers
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
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:
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.
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
