Calculate workday date and time based on hours

[rdd2]

Column B = projected hours
Column C = actual hours
Column D = start date
Column E = finish date based on projected, then actual

Objective: Finish date to calculate based on projected hours for current and all future work orders listed. Start and finish dates automatically adjust based on actual hours. Weekends and non-working hours excluded.

[dflak]

See if this helps: http://www.utteraccess.com/wiki/Work...-_NetWorkHours

[rdd2]

See if this helps: http://www.utteraccess.com/wiki/Work...-_NetWorkHours

Thanks dflak, I'll check your reference.

I was able to get mostly what I wanted in the attached sheet. Still needs tweaking though.

[rdd2]

I have reached the limit of my high school education. Can someone tell me why I'm getting the #VALUE! error?

[dflak]

The formula you have references column C. The formula is expecting to have a numeric value in this cell. However, sometimes you have the null string.

You can change this formula: =IF(F9>0,B9-F9,"")
to: =IF(F9>0,B9-F9,0)

or wrap the whole big formula in an if statement checking F9 or wrap it in IFERROR().

[rdd2]

The formula you have references column C. The formula is expecting to have a numeric value in this cell. However, sometimes you have the null string.

You can change this formula: =IF(F9>0,B9-F9,"")
to: =IF(F9>0,B9-F9,0)

or wrap the whole big formula in an if statement checking F9 or wrap it in IFERROR().

That fixed the error, thanks dflak!

Now another issue. I'm getting a 'Divide by Zero Error' whenever a number ending in 8 or 9 is entered into column F. Why would only those two numbers cause an error?

[Bo_Ry]

Pleasd try at E3

=IF(A3="","",WORKDAY(D3,INT(F3/10),Info!$A$2:$A$11)+MOD(D3+MOD(F3,10)/24,1)+(MOD(D3+MOD(F3,10)/24,1)>Info!$D$2)*(1-Info!$D$2+Info!$C$2))

[rdd2]

Pleasd try at E3

=IF(A3="","",WORKDAY(D3,INT(F3/10),Info!$A$2:$A$11)+MOD(D3+MOD(F3,10)/24,1)+(MOD(D3+MOD(F3,10)/24,1)>Info!$D$2)*(1-Info!$D$2+Info!$C$2))

SWEET!!! Thanks so much Bo_Ry!!! Works perfect, and much simpler!