Completion date formula

[turtel]

Hello All,

First and foremost Happy Friday to everyone.

I am trying to get an approximate date of completion for a project. Which formula can I use to calculate the completion date if each sample takes about 20 minutes to analyze.

Thanks

 

[NARAYANK991]

Hi ,

Something like this ?

Narayan

 

[SirJB7]

Hi, turtel!
The only date at cell I6 doesn't have a time part, and besides it's necessary to know a few things:
a) operation time: 24x7, or 9 to 5, or anything else?
b) should we consider only value at cells H18:H19 or those at columns J:K too? if so, how?
c) tasks are done simultaneously or sequentially?
Regards!

 

[turtel]

Hi SirJB7,

Thanks for the quick response. a)weekday 8-5 b)Only H19 is needed. Also start date will vary from cell to cell depending on when the date the last batch was scored.

Thanks.

 

[SirJB7]

Hi, turtel!
So if start dates vary for each task, it's important to define c). I.e., a task might start before previous tasks had ende, all tasks might start at the same time, is there a maximum capacity at any stage?
Regards!

 

[turtel]

Hi SirJB7,

Let's keep simple, start date will be let's say cell I5. Completion date just depends on the number on cell H19.

Thanks.

 

[SirJB7]

Hi, turtel!

Give a look at the uploaded file. The final formula is this:
H20: =I6+ENTERO(H19*(1/24/3)*24/9)+(8/24)+H19*(1/24/3)-ENTERO(H19*(1/24/3)*24/9)*9/24 -----> in english: =I6+INT(H19*(1/24/3)*24/9)+(8/24)+H19*(1/24/3)-INT(H19*(1/24/3)*24/9)*9/24

Which is build as follows:
H21: =H19*(1/24/3)
H22: =ENTERO(H21*24/9) -----> in english: =INT(H21*24/9)
H23: =H21-H22*9/24
H24: =I6+H22+(8/24)+H23
where H24 is the step by step formula equivalent to that of H20.

Just advise if any issue.

Regards!

 

[turtel]

Hi SirJB7,

Thank's a lot for the formula. It works, however it seems to include weekends. So how do I account for the weekends?

Regards,
Turtel

 

[SirJB7]

Hi, turtel!

Then try using the function WORKDAY. Check uploaded file for formulas at column I:
I24: =DIA.LAB(I6;H22)+(8/24)+H23 -----> in english: =WORKDAY(I6,H22)+(8/24)+H23
I20: =DIA.LAB(I6;ENTERO(H19*(1/24/3)*24/9))+(8/24)+H19*(1/24/3)-ENTERO(H19*(1/24/3)*24/9)*9/24 -----> in english: =WORKDAY(I6,INT(H19*(1/24/3)*24/9))+(8/24)+H19*(1/24/3)-INT(H19*(1/24/3)*24/9)*9/24

Regards!

 

[turtel]

Hi SirJB7,

Sorry for the late reply but thank you very much for the formula.

Regards,
Turtel

 

[SirJB7]

Hi, turtel!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!