IF OR AND statement in one formula

[Simmo1313]

Hi all,

Having a bit of difficulty here figuring out where I am going wrong with this one. The table below is for a lighting audit. The inputs are in red and the formula in cells J2:AG4 are for monday only. The data set continues AH and onward for each day of the week.

I want to be able to input a "1" in the days Monday to Sunday where the lights are on and I want to put the time which they turn on in column H and when they turn off in column "I"

The formulas in the table J2:AG4 should insert a value of "1" if the light is on at that time.

The formula I have at the moment can do that only if the start time is earlier in the day than the finish time so I'm not sure where my formula is incorrect


Example formula for what I have at the moment is in J2 <i2,a2=1,j1><i2),and(h2>


<i2)),1,0))<i2,b2=1,j1><i2),(and(h2><h2,k2><i2,a2=1,j1><i2),(and(h2><h2,j1>=IF ( OR ( AND ( H2 < I2 , A2 = 1 , J1 > H2 , J1 < I2 ) , ( AND ( H2 > I2 , A2 = 1 , J1 < H2 , J1 > I2 ) ) ) , 1 , 0 )

Sorry, had to put the spaces in so that the post would leave my formula as is.

I'm hoping I should return results displayed below once the formula is correct.



[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[TD]AE[/TD]
[TD]AF[/TD]
[TD]AG[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]M[/TD]
[TD]T[/TD]
[TD]W[/TD]
[TD]Th[/TD]
[TD]F[/TD]
[TD]Sa[/TD]
[TD]Su[/TD]
[TD]ON[/TD]
[TD]OFF[/TD]
[TD]12am[/TD]
[TD]1am[/TD]
[TD]2am[/TD]
[TD]3am[/TD]
[TD]4am[/TD]
[TD]5am[/TD]
[TD]6am[/TD]
[TD]7am[/TD]
[TD]8am[/TD]
[TD]9am[/TD]
[TD]10am[/TD]
[TD]11am[/TD]
[TD]12pm[/TD]
[TD]1pm[/TD]
[TD]2pm[/TD]
[TD]3pm[/TD]
[TD]4pm[/TD]
[TD]5pm[/TD]
[TD]6pm[/TD]
[TD]7pm[/TD]
[TD]8pm[/TD]
[TD]9pm[/TD]
[TD]10pm[/TD]
[TD]11pm[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]

[/TD]
[TD]

[/TD]
[TD]7pm[/TD]
[TD]7am[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1

[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]

[/TD]
[TD]5am[/TD]
[TD]9pm[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]7am[/TD]
[TD]8pm[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Any help would be appreciated.</h2,j1></i2),(and(h2></i2,a2=1,j1></h2,k2></i2),(and(h2></i2)),1,0))<i2,b2=1,j1></i2),and(h2></i2,a2=1,j1>

 

[Perpa]

I don't think that is the reason because I have actually entered 00:00, 01:00, 02:00 through to 23:00 which excel does recognise as times because it displays 12 am ,1 am into the cell.

I got it to work doing the following.

I inserted 3 columns above the date and time picker like below

[TABLE="width: 956"]
<tbody>[TR]
[TD][TABLE="width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]M[/TD]
[TD]T[/TD]
[TD]W[/TD]
[TD]TH[/TD]
[TD]FR[/TD]
[TD]SA[/TD]
[TD]SU[/TD]
[TD]T/ON[/TD]
[TD]T/OFF[/TD]
[TD]0:00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]IF ( AND ( A6 = 1 , H6 < I6 , J1 > H6 , J1 < I6 ) , 1 , 0)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]IF ( AND ( A6 = 1 , H6 > I6 , J1 > H6 ) , 1 , 0 )[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]IF ( AND ( A6 = 1 , H6 > I6 , J1 < I6 ) , 1 , 0 )[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]7 PM[/TD]
[TD]7AM[/TD]
[TD]SUM ( J3 : J5 )[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I then hide rows 3 , 4 and 5 and copy the formula through for all the dates.

I think the issue was I was trying to have 2 formulas in one cell which contradicted the results of each other.

If anyone has a neater solution then I am all ears....

Simmo1313,
Got it!
On a copy of your worksheet, make sure all the times are entered 'Number-space-AM or PM'.
Format the times to be 24-hour type, ie. '1:00 PM' is '13:00' etc.
Change the value in cell J1 to be the number 24.

Enter the following formula in cell J2:

=IF(AND(OR($A2=1, $B2=1, $C2=1, $D2=1, $E2=1, $F2=1, $G2 = 1),J$1<>$I2,IF($H2>$I2,OR(J$1>=$H2,J$1<$I2),AND(J$1>=$H2,J$1<$I2))),1,"")

Then copy it across to AG2, then copy J2:AG2 down to J3:AG4.
If you still want AM/PM times, insert a row above Row A and put them there.
Not too bad once I remembered the format issue with times.
Happy 'Lighting Audit'!

Perpa