Need to calculate future date based on standard duration and input

[Jonathan Horvath]

Hi, I work in a development organization and we take our products through
various "stages"... here are the variables:

1) We start at Stage 1 and Stage 5 means 'ready to ship'
2) We have 3 different product classifications, A, B, C
3) Each classification has a 'standard duration' from various Stages to
Stage 5 (i.e. Stage 1 to Stage 5 for a Class A is 18 months, Stage 2 to Stage
5 for a Class A is 12 months, Stage 1 to Stage 5 for a Class C is 12 months,
etc....

I have a big table in Excel, each Row contains 1 product and lists the
product Class, Stage 1 scheduled date, Stage 1 Actual date, Stage 2 date,
Stage 3 date, Stage 4 date

I need to calculate for each product at any given time the expected Stage 5
date based on the standard duration from the last Stage passed (or the Stage
1 scheduled date for pre Stage 1 products).

I cannot do this in Excel with a formula because there are too many nested
"if" statements... so I'm searching for a way to do this with VBA...

Any help?

thanks!

Jon

 

[Steve]

This could be easily be built in a an Access database. If you would like to
build a database to do what you describe, contact me.

Steve
(e-mail address removed)

 

[cht13er]

Hi, I work in a development organization and we take our products through
various "stages"... here are the variables:

1) We start at Stage 1 and Stage 5 means 'ready to ship'
2) We have 3 different product classifications, A, B, C
3) Each classification has a 'standard duration' from various Stages to
Stage 5 (i.e. Stage 1 to Stage 5 for a Class A is 18 months, Stage 2 to Stage
5 for a Class A is 12 months, Stage 1 to Stage 5 for a Class C is 12 months,
etc....

I have a big table in Excel, each Row contains 1 product and lists the
product Class, Stage 1 scheduled date, Stage 1 Actual date, Stage 2 date,
Stage 3 date, Stage 4 date

I need to calculate for each product at any given time the expected Stage 5
date based on the standard duration from the last Stage passed (or the Stage
1 scheduled date for pre Stage 1 products).

I cannot do this in Excel with a formula because there are too many nested
"if" statements... so I'm searching for a way to do this with VBA...

Any help?

thanks!

Jon

To avoid nested ifs, build a table somewhere, listing all the
different permutations of classes, start stage, and end stage ... and
use vlookups.

That should work, I think..

Chris

 

[Jonathan Horvath]

That woud work, but I have a couple of additional constraints I forgot to
manage, I'm responding to you and Steve here...

1) I need to do this in Excel so I can share it with others
2) I need to avoid using vlookups because not everyone has those installed
in Excel - and have you ever tried to get an Executive to do something in
Excel other than open a file and start screaming ???

Jon