Easy Formula Stumping Me - Easy =IF() statement

[spcalan]

I have 1 column I want to indicate either "On track" or "At Risk"..

A1 is a ship date
So B2 is the status from above..

H is Release Date
I is Kit Date
J is Start Date
K is Finish Date

So the logic is this :

If Finish Date + 2days <Ship Date = "On Track"
if Start Date + 3 days <Ship Date = "On Track"
If Kit Date + 4 days <Ship Date = "On Track:
if Release Date + 5 days <Ship Date ' On Track"
These 4 stages will always have data ( or " " values ) because of Vlookups.. so no 'blank cells".

So basically...
if the finish date is blank, the start date is blank, the kit date is blank, but the release date +5 days is < ship date = we are still On Track..

Help.

 

[spcalan]

perfect... it works when I manually type in dates...
But the dates will be populated by Vlookups..

So either the vlookup will return a date
or - Error ( which I will turn the error into a "" value using a =if(iserror() function.

So now I have to rethinkg this through..

So now..
Release Date = The first date will always have a date ( manually keyed ),
Next, must check to see if the Kit date has a value, if so - use the logic, if not, use today's date + X
Next, must check to see if the Start date has a value, if so - use the logic, if not, use today's date + x
Next, must check to see if the Finish date has a value, if so - use the logic, if not, use today's date + x
Next, must check to see if the Dyno date has a value, if so - use the logic, if not, use today's date + x
Next, must check to see if the Paint date has a value, if so - use the logic, if not, use today's date + x

This is beginning to be a booger to build...

 

[Jonmo1]

But the dates will be populated by Vlookups.

That's irrelevant.
Dates are Dates, wether they are typed in by hand or returned by a vlookup.
That vlookup got the date from somewhere right?

Now if your vlookup is returing a TEXT string that just looks like a date, then that's the issue you need to address.
Can you post the formula(s) returning the dates in question?

 

[spcalan]

That's irrelevant.
Dates are Dates, wether they are typed in by hand or returned by a formula.

Now if your vlookup is returing a TEXT string that just looks like a date, then that's the issue you need to address.
Can you post the formula(s) returning the dates in question?

What I meant was.. There are 6 stages. Very linear....
Step 1 - Release date = manually keyed and will always be a date value - always...
Step 2 - Kit date = vlookup. Either returns a date, or "" from an if(iserror()) formula. The blank means it either doesn't exist, or the stage hasn't started yet.
Step 3 - Start date = vlookup. Either returns a date, or "" from an if(iserror()) formula
Step 4 - End date = vlookup. Either returns a date, or "" from an if(iserror()) formula
Step 5 - Dyno date = vlookup. Either returns a date, or "" from an if(iserror()) formula
Step 6 - Paint date = vlookup. Either returns a date, or "" from an if(iserror()) formula

So all entries will have a release date - always
Step 2 - either date from the vlookup.. or a "" when it hasn't started in that stage yet.
etc...

But my status formula from earlier only works the logic IF there are dates, not ""..

not sure how this is going to work.

 

[Jonmo1]

Can you change those if(iserror()) formulas to return 0 instead of "" ?

If hiding 0's is important, go to File - Options - Advanced - Display Options for this worksheet
UNcheck "Show a zero in cells that have zero values"

 

[spcalan]

Other thoughts..

How about this idea :

if the 6 stages have a date, then use the date.. if not use today's date in the calculation to determine risk... ?

You would have to check each stage, and if the value was "" - then use today's date... based on that particulur stages +X days ?

 

[spcalan]

I am going to use a helper column to determine which stage we are in.. then based on that value, run the logic..

So if I have 5 stages, help[ me with a formula to determine current stage.

K2 - Picking
L2 - Start
M2 - Finish
N2 - Dyno
O - Paint

I need to know the last completed stage..

Example -
If Paint is equal to "" - which means not found, which means not started.. then check Dyno.... then keep checking

Can't do a =count() formula, because the production floor might forget to enter the date for picking, but the others are completed... I tried that approach first.

Looks like a long =if() statement again...

 

[spcalan]

I have it now.
I built a helper column that now tells me what the current stage is.
I will now take that stage and apply the logic to determine status.

I hope there is a short formula I can use to perform this task....

Again..

6 stages

Release Date
Kit Pick
Assembly Start
Assembly Finish
Dyno Finish
Paint Finish

This is what I envision this formula to be :

=IF(the stage is = 'Paint Finish", then take the reported Paint Date Value and add 1 day, then compare if the Paint Date + 1 to the required ship date - Which is is Column H.. then move to the next stage...

Thoughts ?

 

[spcalan]

This formula gives me the 'latest' stage :

=IF(O2 < > "",$O$1,IF(N2 < > "",$N$1,IF(M2 < > "",$M$1,IF(L2 < > "",$L$1,IF(K2 < > "",$K$1,"Only Released")))))