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"

[TABLE="width: 186"]
<tbody>[TR]
[TD="class: xl64, width: 93"]2018-07-01[/TD]
[TD="class: xl65, width: 93"]2018-06-08[/TD]
[/TR]
</tbody>[/TABLE]

 

[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"))