# Excel Multiple Criteria

I have 5 months worth of data that I am trying to find out how many Defects occur within 30, 60, or more than 60 minutes after the defect code 15R occurs.

• Column AX, and AY converted column T and U to integers so that Excel would recognize the imported date and time.
• Column F is used to put the Date and Time into one column (per the suggestion below).
• Row 4 shows the formulas that are used currently.
• Columns C, D, and E were manually updated in the image below but I am looking for a formula so that I do not have to rely on my personal accuracy.

(I pasted in an image because when I go to copy/paste in the worksheet the formatting does not look correct) • If you have two 15R on the same day, then you're always going to get `TRUE` for all three conditions. Can you clarify what you are looking for? What do you expect the output to look like (can you perhaps mock up the output you are looking for?) Also, instead of posting a picture, can you copy/paste the table, so we can import your data to test with? Jun 15, 2016 at 17:10
• Might look at combining date & time into one cell and converting date/time to linux epoch
– Sun
Jun 15, 2016 at 17:40

I don't quite understand why you need AX and AY, because if T and U contain real date and real time values (as opposed to text), then you can just use these in F.

But anyway, with one more helper column you could get to this: I inserted a new column B, which shifted the other columns to the right. The formulas in row 2 are

``````B2 =IF(A2="15R",G2,B1)
C2 =G2-B2
D2 =IF(AND(C2>0,C2<TIME(0,30,0)),"yes","")
E2 =IF(AND(C2>TIME(0,30,0),C2<TIME(0,60,0)),"yes","")
F2 =IF(C2>TIME(0,60,0),"yes","")
``````

Copy down.

• Thank you so much!! That worked perfectly! (Columns U and V are imported into excel from a different program and excel does not recognize them as date or time formats so I had to convert the data so that I could use it for other pivot tables I use this data for). Jun 16, 2016 at 11:20