#### Dclem

##### New Member
I have been trying to figure out a formula to show how many days a project would take if a start date was given and hours of project assuming a person worked 8 hrs per day. See example below. I used the formula
MIN(D\$3-SUM(\$F\$3:G3),8), which will "spread" the "8" until it reaches "0", but when i tried to trigger when a date is reached, =IF(C\$3>=J\$1,MIN(D\$3-SUM(\$F\$3:G3),8),0), it only shows 1 date with an "8" in it. I tried calculating end date using WORKDAYS(C\$3,D\$3/8) which is 1/15/2020, but couldn't figure out how to stop at end date. Is this possible? Examples of how data should look below.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### arthurbr

##### Well-known Member
Starting in F1 try :=IF(\$C3>=F\$1,MIN(\$D3-SUM(\$E3:E3),8),0)
pull right and down

#### juddaaaa

##### Board Regular
Try this
Rich (BB code):
``=IFERROR(IF(AND(F\$1>=\$C3,DATEDIF(\$C3,F\$1,"D")<\$D3/8), 8, ""),"")``
Book1
CDEFGHIJKLMNOPQRSTUVW
1Date30/12/201931/12/201901/01/202002/01/202003/01/202004/01/202005/01/202006/01/202007/01/202008/01/202009/01/202010/01/202011/01/202012/01/202013/01/202014/01/202015/01/202016/01/2020
2Project StartHrs
303/01/202072

888888888

409/01/202032

8888

506/01/202048

888888

Sheet1
Cell Formulas
RangeFormula
F3:W5F3=IFERROR(IF(AND(F\$1>=\$C3,DATEDIF(\$C3,F\$1,"D")<\$D3/8), 8, ""),"")

#### juddaaaa

##### Board Regular
Rich (BB code):
``=IFERROR(IF(AND(F\$1>=\$C3,NETWORKDAYS(\$C3,F\$1)<=\$D3/8), 8, ""),"")``
Book1
ABCDEFGHIJKLMNOPQRST
1Date30/12/201931/12/201901/01/202002/01/202003/01/202006/01/202007/01/202008/01/202009/01/202010/01/202013/01/202014/01/202015/01/202016/01/2020
2Project StartHrs
303/01/202072

888888888

409/01/202032

8888

506/01/202048

888888

6
7
Sheet1
Cell Formulas
RangeFormula
F3:S5F3=IFERROR(IF(AND(F\$1>=\$C3,NETWORKDAYS(\$C3,F\$1)<=\$D3/8), 8, ""),"")

#### Dclem

##### New Member
Juddaaa that worked....never would have thought of doing it that way! Thanks.

#### juddaaaa

##### Board Regular
You're very welcome.

### Forum statistics

1,095,235
Messages
5,443,278
Members
405,223
Latest member
Industrial_Eng_SA

### This Week's Hot Topics

• Copy entire row if CountA <>0 to another sheet
[B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
• Select last used Row in Table
I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
• excel workbook: do not allow certain file name
Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
• fixing problem autofilter
hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
• “Weight”
Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
• How to capitalize everything before a certain character?
In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...