I am working on a project for work that involves showing metrics for dates. I am trying to workout my function statements and need a little help. Thank you for reviewing the below work and contributing.

Lets first assume that todays date is 1/8/2016.

Example 1:
Cell A1(Planned RFU) has a date: 1/1/2016
Cell B1(Current RFU) has a date: 1/3/2016
Cell C1(Actual RFU) has a date: 1/3/2016

Example 2:
Cell A1(Planned RFU) has a date: 1/1/2016
Cell B1(Current RFU) has a date: 1/3/2016
Cell C1(Actual RFU) has a date: 1/1/2016

Example 3:
Cell A1(Planned RFU) has a date: Blank value
Cell B1(Current RFU) has a date: Blank value
Cell C1(Actual RFU) has a date: 1/3/2016

Example 4:
Cell A1(Planned RFU) has a date: 1/1/2016
Cell B1(Current RFU) has a date: Blank value
Cell C1(Actual RFU) has a date: 1/3/2016


Logic, in the first example above. If an item was planned to complete on 1/1/2016 (planned RFU date) and then the date moved to 1/3/2016 (Current RFU Date), and then the item was completed on 1/3/2016 (actual RFU), then therefor the item was completed on time.


In example 1, the item, was completed on time based on the Current RFU date and the Actual RFU date.

In example 2, the item was late based on the Current RFU date and the Actual RFU date.

In example 3, the item, was completed on time based on the blank date/value for Planned RFU date and the blank date value for Current RFU date, therefor making the Actual RFU date the only date.

In example 4, the item, was completed late (or could be on time if you change the date for Planned RFU) based on the Current RFU date as blank value and the Actual RFU date has a date.

I am tracking all the date as a comparison of the Planned and Current dates against the Actual date.

Here are the statements I have formed (below), for if the item is late or the item is on time. But I am having a hard time incorporating the other 2 examples (3 and 4 above) into the function statement. If there is no value in Planned or Current then the item should show as on time as the only date entered is Actual RFU. And if there is no date in Current RFU then the Planned RFU should be compared to the Actual RFU. And vice versa with planned and actual.

Was the item completed on Time?
=IF(AND([@[Act RFU]]<"",[@[Act RFU]]<=[@[Cur RFU]]),1,0)


Was the item completed Late?
=IF(AND([@[Act RFU]][@[Cur RFU]],[@[Cur RFU]]<"",[@[Act RFU]]0),1,0)

Is the project still open and its past the planned or current RFU date, (no actual RFU date)?
=IF([@[Cur RFU]]=TODAY(),0,IF([@[Act RFU]]="",1,0))




Any help would be greatly appreciated. Thank you.

Marty