IF Function with Variable Dates

[olimajor123]

Hi,

I am trying to use the IF formula with some variable dates and have tried a few combinations but have been unable to get it to work correctly for all the cells yet.

Basically I have two columns titled 'Due Date' and 'Cleared Date' and a third blank column. In the third column I want to compare whether the job was cleared before, after or on the due date. I have about 1000 rows so needless to say I don't want to do this manually!

Any help is appreciated.

Thanks

 

[shg]

=choose(sign(dateCleared - dateDue) + 2, "Early", "On Time", "Late")

Copy down.

 

[Dreadknight Nasus]

Assuming your Due Date is column A and Cleared Date is column B, here's your formula:

<a2, "before",if(b2=""><a2, "before",if(b2=""><a2, "before",if(b2="">=IF(B2<a2, "before",if(b2="">A2,"After","On"))

Copy this formula down as far as you need and the ranges will automatically adjust accordingly.

Edit: Okay for some reason my formula won't copy paste correctly. When I click "save" or "submit", half the formula goes missing on the post.</a2,></a2,></a2,></a2,>

 

[shg]

Add a space after < so it isn't interpreted as the start of a tag.

 

[excelbadman87]

Np

 

[olimajor123]

Thanks Shg. That worked

Hey Ste

 

[Dreadknight Nasus]

Add a space after < so it isn't interpreted as the start of a tag.

Oh okay, thanks