IF Function?

[Leehartnell]

Good Afternoon All,

First time poster, long time lurker! Great forums helped me out many times! Hopefully someone can help with this quesitons?

I have created an action tracker in Excel and I've used a formula to indicate if actions have past their target completion date.

The formula i have used is:

=IF(ISBLANK(G3), IF(G3<TODAY(),1,0))

So if the date hasnt been passed the result cell will display 0 and if it has been passed it will display 1. As i have multiple actions i can then total this to indicate the total number of actions past their completion date. This works well for me and also links into other worksheets.

The issue I have is that if the action has been completed it will still show as past the completion date (once the date has passed obviously). Is there anyway of adding another IF statement to say that if another cell in that worksheet = 'Complete' then a zero will be displayed?

Thanks in advance!

Lee

 

[Leehartnell]

Re: Help Needed! IF Function?

Sorry guys for some reason it keeps deleting part of the formula i have used: Please see below

=IF(ISBLANK(G3), IF(G3<TODAY(),1,0))

 

[Scott T]

Re: Help Needed! IF Function?

If you have a < in your formula put spaces around it. The forum gets confused and thinks you are using HTML code.

 

[dreid1011]

Re: Help Needed! IF Function?

Is it at all possible for "Complete" to show up if it is not past the completion date? User entry error or the like? You could use an OR() at the beginning like this if that is not a concern:

=IF(OR(ISBLANK(G3),L3="Complete"),IF(G3 ....... etc.

Otherwise, using AND() on your second IF statement checking for past completion and "Complete" and the false result return 1?

 

[Leehartnell]

Re: Help Needed! IF Function?

For some reason it will not display the full formula i have used?

=IF(ISBLANK(G5), "",IF(G5<TODAY(),1,0))

The cell that will contain the text - COMPLETE is manually input from a drop down list. So yes there can be user entry error but it will be protected workbook that only i have access to update.

 

[Leehartnell]

Re: Help Needed! IF Function?

Sorry about that guys... thought i was going crazy for a minute! The formula i have used is:

=IF(ISBLANK(G5),"",IF(G5 < TODAY(),1,0))

 

[Scott Huish]

Re: Help Needed! IF Function?

That can be shortened to this:

=IF(G5="","",(G5<TODAY())+0)

Is it working for you?

 

[dreid1011]

Re: Help Needed! IF Function?

Sorry about that guys... thought i was going crazy for a minute! The formula i have used is:

=IF(ISBLANK(G5),"",IF(G5 < TODAY(),1,0))

Perhaps one of these then based on my previous post:

=IF(OR(ISBLANK(G5),L5="Complete"),"",IF(G5 < TODAY(),1,0))

or

=IF(ISBLANK(G5),"",IF(AND(G5>=TODAY(),L5="COMPLETE"),0,1))

Change L5 to the cell where "Complete" will be.

 

[Leehartnell]

Re: Help Needed! IF Function?

Fantastic! Thank you very much!

 

[dreid1011]

Re: Help Needed! IF Function?

Fantastic! Thank you very much!

You are very welcome.