Formulate to filter 3 answers.

New Contributor

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 

4 Replies
best response confirmed by Hans Vogelaar (MVP)


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

Does this formula return your expected results?

work complete.JPG 

Thank you, your a star
Hi Again,

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


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.