SOLVED

Excel IF formula

Copper Contributor

Hi I am trying to do an if formula as follows:

 

Delivery Date            Due Date          Status

25/07/2021               28/07/2021      

01/07/2021               05/07/2021

01/01/1970               06/07/2021

01/01/1970               15/07/2021

 

In the status column I want to see the text "Early" if the Delivery Date is earlier than the due date

In the status column I want to see the text "Later" if the Delivery Date is later that the due date

In the status column I want to see the text "on time" if the dates match

My problem is that where the Delivery Date says 01/01/1970 I want the status to say "Open"

The date 01/01/1970 is earlier than my due dates so this is causing a problem

Can anyone help me

Thanks

Ann

3 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@anncshaw 

In Status

image.png

it could be

=IF( [@[Delivery Date]]=DATE(1970,1,1), "Open",
 IF( [@[Delivery Date]]<[@[Due Date]], "Early",
 IF([@[Delivery Date]]>[@[Due Date]], "Later", "On time")))
Amazing thank you!!!!!!

@anncshaw , you are welcome

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@anncshaw 

In Status

image.png

it could be

=IF( [@[Delivery Date]]=DATE(1970,1,1), "Open",
 IF( [@[Delivery Date]]<[@[Due Date]], "Early",
 IF([@[Delivery Date]]>[@[Due Date]], "Later", "On time")))

View solution in original post