CODEWITHSUNDEEP
excelexcel-formula
Simon
Simon

Reputation: 13

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

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

enter image description here

Upvotes: 1

Views: 2272

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34220

Well this is what I was thinking. If you had an irregular work pattern and still wanted to find how many days had been worked (counting any part days left over at the end as an extra day), you could work out a running total using scan and use match to find how many days that was equal to. There are two cases:

(1) where it's an exact number of days, e.g. the number of hours is 9, 18 etc.

(2) where it's not an exact number of days, e.g. the number of hours is 10, 20 etc.

But then you have to ignore the days with zero hours (like weekends).

=LET(range,I$6:R$6,
hours,G11,
runningTotal,SCAN(0,range,LAMBDA(a,b,a+b)),
lastDay,IFERROR(MATCH(hours,runningTotal,0),MATCH(hours,runningTotal,1)+1),
COUNTIF(INDEX(range,1):INDEX(range,lastDay),">0"))

enter image description here

EDIT

For the case where hours is less than the hours on the first day,

=LET(range,I$6:R$6,
hours,G11,
runningTotal,SCAN(0,range,LAMBDA(a,b,a+b)),
lastDay,IF(hours<INDEX(range,1),1,IFERROR(MATCH(hours,runningTotal,0),MATCH(hours,runningTotal,1)+1)),
COUNTIF(INDEX(range,1):INDEX(range,lastDay),">0"))

EDIT 2

For the case when hours is blank or zero:

=LET(range,I$6:R$6,
hours,G11,
runningTotal,SCAN(0,range,LAMBDA(a,b,a+b)),
lastDay,IF(hours<INDEX(range,1),1,IFERROR(MATCH(hours,runningTotal,0),MATCH(hours,runningTotal,1)+1)),
IFS(hours="","",hours=0,0,TRUE,COUNTIF(INDEX(range,1):INDEX(range,lastDay),">0")*(hours>0)))

Upvotes: 1

Thomas C.
Thomas C.

Reputation: 217

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.

EDIT : To count a day when total work hours represent some days + some hours (less than a day)

=5*QUOTIENT(A1;40)+QUOTIENT(MOD(A1;40);9)+QUOTIENT(MOD(MOD(A1;40);9);4)+IF(MOD(MOD(MOD(A1;40);9);4)<>0;1;0))

Upvotes: 1

Related Questions