0

0

I need help figuring out a formula. In this example: Column E is Start and F is Finish. I want to write a formula in column J that writes an "X" with the following conditions:

  • E > 7:00 a.m.
  • The time difference between F with the last "X" written and the next E is at least 30 min. (F has to be before E).

In the example shown only J15 and J22 need to have an "X" but can't figure out how to write the formula (ignore "#N/A" in J16).

enter image description here

5
  • Please share a sample spreadsheet, even if it only includes the date-time data in E1:F22 (as shown above with the addition of Rows 1 - 5). Otherwise, you're asking the volunteer contributors here to manually type the data into one of their own spreadsheets themselves before they can even begin to start thinking about a solution; and that will be a deterrent to most.
    – Erik Tyler
    Dec 2 '21 at 4:10
  • Sorry here's a sample spreadsheet: docs.google.com/spreadsheets/d/…
    – ALEX
    Dec 2 '21 at 14:46
  • Please show your hand-entered expected results in the sample spreadsheet, and in say five rows where you enter an 'X', explain why that row should show an 'X'. Also in say five rows where you do not enter an 'X', explain why that row should not have an 'X'. Dec 2 '21 at 19:54
  • I see that another contributor ("doubleunary") has left you a proposed solution. Please report back on that. If that solution is not what you had in mind, please follow doubleunary's instructions in the comment above and then comment here that you've done so.
    – Erik Tyler
    Dec 2 '21 at 20:48
  • I should also point out that your "date-times" in your sample sheet are actually strings, not usable date-times. Is this reflective of your real sheet data? If so, it presents additional complications. You can test this by typing into any open cell =ISNUMBER(A2) (replacing A2 with the actual reference of the date-time you want to check). If the return is FALSE, then your data is actually string data, not real date-time data.
    – Erik Tyler
    Dec 2 '21 at 21:28
0

The problem is not very well defined, but see if the 'test' sheet gets the results you want. It uses these formulas:

=filter( value(A2:B), timevalue(A2:A) > timevalue("7:00") )

=+iferror( filter(G3:G, H2 < G3:G - "0:30:00") )

=if( I2 <> I3, I2, iferror(1/0) )

=arrayformula( 
  if( 
    isnumber(J2:J), 
    "X", 
    iferror(1/0) 
  ) 
)

=arrayformula( 
  iferror( 
    vlookup(
      G2:G, 
      J2:K, 
      columns(J2:K), 
      false 
    ) 
  ) 
)

=arrayformula( 
  if( 
    len(K2:K) + len(L2:L), 
    "X", 
    iferror(1/0) 
  ) 
)

=arrayformula( 
  iferror( 
    vlookup(
      trim(A2:A) & trim(B2:B), 
      { trim(G2:G) & trim(H2:H), M2:M }, 
      2, 
      false 
    ) 
  ) 
)

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.