Nested If statement with conditional formatting

[rustym]

Hi everyone,

I am working a spreadsheet with actions and deadlines in Excel 2003. In column I is the original due date, column J contains new due date and column K contains completed date. I need to add a status column that clearly shows if the action has been completed, is on going or if it's late. My idea is to create a nested if statement that returns a 1 if the action is completed, 2 if its on going, and a 0 if it's late. From there I can use conditional formatting to make the status cell green for completed, red for late, etc.

The statement that I came up with is the following but it keeps giving me an error.

IF(k5 < TODAY(), 1 , IF(or(j5 > today(); i5 > today()), 0,2))))

Any help would be greatly appreciated.

 

[WaterGypsy]

When I pasted your fromula into excel it didn't like the ; and I changed it to , then it worked

 

[Kaps_mr2]

You have put a semi-colon where there should be a comma - see below. Thanks

=IF(K5<TODAY(),1,IF(OR(J5>TODAY(),I5>TODAY()),0,2))

regards

Kaps

 

[rustym]

Hi WaterGypsy:

I changed the semi colon to a comma but I am still receiving the same error. The message pops up and it highlights ,2,If . I think for some reason it is not accepting anything other than a single logical test and I don't know why.

 

[WaterGypsy]

This is straight from my test Rustym..... maybe try pasting that into your sheet and seeing what happens?

=IF(K5<TODAY(),1,IF(OR(J5>TODAY(),I5>TODAY()),0,2))

 

[rustym]

This is straight from my test Rustym..... maybe try pasting that into your sheet and seeing what happens?

=IF(K5<TODAY(),1,IF(OR(J5>TODAY(),I5>TODAY()),0,2))

Can you please re paste your code because I can only see a part of it I think. I need three different outputs (2, 1 and 0) from the If statement and right now your expression only returns a 0 or a 2.

Thanks again.

 

[WaterGypsy]

Everytime I enter it and send it removes some of the code

 

[rustym]

I put a space between the arguments and it showed the whole code, maybe try that.

 

[WaterGypsy]

= if ( k5 < today() , 1 , if ( or ( j5 > today() , i5 > today() ) , 0 , 2 ) )

lots of spaces added in seems to work ......

 

[rustym]

Thanks for the help but for some reason your formula still returns an error and I have no idea why. I think it's something that's wrong with my settings or possibly formula properties but I don't know how to go about fixing it.