Hi,
Say I have in a cell showing 33:00 for 33 hours. Which are hours spent on a project. I would like to convert it to show something similar to 4 days 5 hours or 4:5:00 based on the assumption there is 7 work hours /day.
Any idea how to do it?
Thanks
Hi bern,
there are text in the cell or a value formatted as hours.
Ok I got it!
On the sum of hours, as shown in the question, 33:00 (format defined as [HH]:MM), I apply the two following formulas:
First one to find the number of days of 7 hours:
=INT((*cell**24)/7)
Second one to find the number of remaining hours:
=MOD((*cell**24),7)
Then I combine these formulas inside a concatenate :
=CONCATENATE(INT((cell*24)/7)," days “,MOD((cell*24),7),” hours")
Which gives : 4 days 5 hours
Voilà!
P.S. If someone has something simpler than this, please let me know!
In my calculation, 33 hours is 1 day and 9 hours, not 4 days and 5 hours
As said in my post, it is based on 7 hours per workday : ( 7 hours * 4 days ) + 5 hours = 33
My formula is different from yours, like this (assume the value in cell A1 is 33:00:00) :
=INT(A1)&" days “&TEXT((A1-INT(A1))*24;”#.00")&" hours"
Your formula give 1 day 9 hours for 33:00:00. As said in my post, I want 7 hours per day not 24. It is the number of workhours in a day.