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). • 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. Dec 2 '21 at 4:10
– 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. 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. Dec 2 '21 at 21:28

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