Date calculation help please!

[Cam]

Hello,

I am trying to find a formula to calculation the estimated completion date
for different operations which returns the date excluding holiday working 7
days a week @ 19 hours per day, but couldn't seemed to find the answer how.

Column A1 is the startdate.
Column B is the production time in days required to completed.
Column C starting C3 is the calculated value of completion date in date time
format.

Here is the sample table:
A B C
1/2/2009 6:00 AM StartDate
Opr ProdTime (Days) ComplDate
10 2.045 1/6/09 1:04 AM
20 0.143
30 0.667
40 1.611
50 0.667

Thank for sugguestions

 

[Bob Phillips]

This gives you the result you posted

=WORKDAY($A$1,INT(B3))+MOD(B3,1)

but I don't get why it is 01:04 AM and not 07:04 AM.

How does 19 hours factor in, what are the 5 non-working hours?

 

[Shane Devenshire]

Hi

The returns the same answer as Bob but uses the 19 hour factor

=A1+SUM(B2:B6)/24*19

Where the start date is in A1 and the days are in B2:B6.

To handle holidays you will need to modify the approach:

=C3-NETWORKDAYS(A1,C3,M1:M5)+NETWORKDAYS(A1,C3)

In this case the first formula is in C3 and you would enter Holidays in M1:M5

 

[Cam]

Thanks Shane & Bob,

I will try out the formula tomorrow. Was ask to do another item which is to
calculation the date return per shift. There are shift, 1st & 2nd shift is
6.5 and 3rd shift is 6 hrs. Do I use the same formula except /24*6.5 for 1 &
2 and /24*6 for 3 shift?

Result wanted:
1/2/2009 6:00 AM StartDate
Opr ProdTime (Days) 1st shift 2nd shift 3rd shift
10 2.045 " "
1/6/09 1:04 AM
20 0.143
30 0.667
40 1.611
50 0.667

 

[Cam]

Bob,

The formula is working except it doesn't exclude holiday. I have a separate
sheet called Holidays with a list of all the holidays in date.
How can I incorporate it into the formula to exclude those holidays? big
thanks.

 

[Bob Phillips]

=WORKDAY($A$1,INT(B3),holiday_range)+MOD(B3,1)

 

[Cam]

Bob,
Thanks, I included the holiday range (in date format), but I don't know why
it is still counting those holidays as workday.
My calendar holiday is not normal US holidays, but I thought it shouldn't
matter if it is based on the holiday list I specified. Any ideal?

 

[Bob Phillips]

I picked up in your new thread.