Formulate to filter 3 answers.

[pwhitfield49]

Hi All,

I am trying to find a way to bring 3 returns on true or false answer.

I am looking at date ranges.

firstly, I am looking to run a KPI report, so data is based on completed with target(on time),not completed in target(overdue) and outstanding( works not complete) this will register outstanding.

I know I need to use formulae below for on time and overdue.

=IF(N2<M2,”overdue”,”on time”)

for the above I have a date for the work completed, and a date for the target date completion, if the works are on time, ie completed before the target date, formula will return on time, however if complete out of target if will return overdue.

I need to filter work not completed- outstanding.

work complete cell, M2 will not have a date entered and I want to return outstanding.

I know I need to use AND, OR, NOT, just don’t know how.

Any help is welcome 

Kind Regards

Peter Whitfield 

[#2 · Accepted solution]

@pwhitfield49 

=IF(M2="","outstanding",IF(N2<M2,"overdue","on time"))

Does this formula return your expected results?

 

[#3]

Thank you, your a star

[#4]

Hi Again,

On a pivot table how do you filter between two date ranges?

[#5]

@pwhitfield49 

That seems to be a new question. Can you start a new discussion with as much information as possible. Best with a sample file or a screenshot of your file and the expected result. Thank you.