How to convert hours to work days, with different working times a day?-CodePudding

I have this problem. I am doing a gantt chart, and I need to convert hours to days. The problem is, that from Monday to Thursday, the working period is 9 hours and on Friday is 4 hours.

I can not figure it out, how to do this. I managed to get hours from days, but not on the contrary. The formula i used to get the hours:

=SUMIF(I10:AQ10;1;$I$6:$AQ$6)

I placed 1 or 0 in the chart to mark the working days.

I am from Czech republic so sorry for the names. Hodiny means hours, Pracovní dny means working days, Po,út... are days

CodePudding user response：

As i said in my comment it isnt a converting problem. You could use euclidian division to find a solution but it could be wrong. Here is my solution (and i guess @Tom Sharpe's too) :

=5*QUOTIENT(A1;40) QUOTIENT(MOD(A1;40);9) QUOTIENT(MOD(MOD(A1;40);9);4) (A1 being the cell where you store work hours)

Basically, it counts the number of full work week (40 hours) and multiply it by 5 (work day in a week) then, which the remaining hours counts the number of 9 work hours-day and then, with the remaining hours counts the number 4 work hours-day.

It only works if most weeks are composed of 4x9 hours days 1x4 hours days. For exemple, if for some reasons you only work on friday for 10 weeks (so 10 days), this formula will only display 5 work days. Hope that this was clear.