Help with if formula with multiple conditions

[willow1985]

i am hoping someone can help me with an IF formula that has multiple conditions. I have tried a few different ways but still I cannot get it to work.


What I require is

if A2="0" then return a blank
if L2 ="0" then return a blank
If A2>L2 return "Late"
If A2 is <=L2 then return "on time"

=IF(A2="0","",IF(L2="0","",IF(A2>L2,"Late",IF(A2<=L2,"On Time",""))))

I do not have the raw data yet and am just preparing a spreadsheet for it so I am not sure if some of the cells will be blanks or "0" so I need a formula that I can adjust between the two

=IF(A2="","",IF(L2="","",IF(A2>L2,"Late",IF(A2<=L2,"On Time",""))))

These formulas do not work but I have enclosed them to give you an idea.

Thank you to anyone who can help!

Carla

 

[Joe4]

An empty cell will be treated the same as zero (so they are as treated-one-and-the-same).
Try:

=IF(OR(A2=0,L2=0),"",IF(A2>L2,"Late","On Time"))

 

[Fluff]

How about
=IF(OR(A2=0,L2=0),"",IF(A2>L2,"Late","On-Time"))

 

[willow1985]

Even if the 0 comes through as text?

 

[Joe4]

Even if the 0 comes through as text?

No, but then try:

=IF(OR(A2=0,A2="0",L2=0,L2="0"),"",IF(A2>L2,"Late","On Time"))

 

[willow1985]

The formula doesnt seem to be recognizing which items are late and labeled everything as on time.

A2 and K2 had the following data but came through as "On Time"

2018-07-01

2018-06-08

<tbody>
</tbody>

 

[Joe4]

Are your dates actually entered as Dates, or are those Text too?
Is that date format "yyyy-mm-dd" or "yyyy-dd-mm"?

 

[willow1985]

Here is a link to a copy of the workbook:

https://www.dropbox.com/s/2llzz3jprlur8j7/Date Diff.xlsx?dl=0

Sometimes the data is given to me as the orange columns and other times the blue. I am preparing for either but it seems like the one with "0" is giving the most issue.

Thank you

Carla

 

[Joe4]

I am not at a location where I can download the file in order to look at it.
Perhaps someone else will be able to take a look at it. Otherwise, it will need to wait until I am at home where I can download the file.

 

[Fluff]

Column D is text not dates, hence the problem.
Try
=IF(OR(C2="0",D2="0"),"",IF(C2< DATEVALUE(D2),"Late","On Time"))