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)

enter image description here

  • 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?
    – BruceWayne
    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

1 Answer 1


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.

enter image description here

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).
    – K.Mack
    Jun 16, 2016 at 11:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.