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>

 

[CROY1985]

Try this in cell J2 (you can then copy it down and across)

=IF($B2=1,IF($H2<$I2,IF(AND(J$1>$H2,J$1<$I2),1,""),IF($I2<$H2,IF(OR(J$1>$H2,J$1<$I2),1,""),"Error")),"Day not 1")

Let us know if it falls short anywhere.

Ive built in some error checking, if the result is: "Day not 1", this means the day is blank, or does not have a 1 in it.
You would need to change the B2 reference to C2 for tuesday. D2 for wednesday etc.

The other error code is "error" this means either time is entered wrong or the same on and of time is used.

What do you want to show if On = 08:00 and off = 08:00?

 

[Perpa]

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>

Simmo1313,
Both portions of your formula evaluate to '1' for the tabled values...
IF(OR(H2 < I2 , A2 = 1 , J1 > H2 , J1 < I2 ), 1, 0 ) = 1
IF(OR(H2 > I2, A2 = 1, J1 < H2, J1 > I20), 1, 0 ) = 1

I tried various combinations of 'IF(OR(AND...' and 'IF(AND(OR...'

For the values shown in your table, the formula below looked like the most promising.
Check the logic and see if this format works for you. Excuse the extra spaces.

=IF ( AND ( OR ( H2 < I2 , A2 = 1, J1 > H2, J1< I2) ,(OR (H2 > I2, A2=1,J1 <H2, J1 > I2) ) ), 1, 0 )

Perpa

 

[Simmo1313]

I tried this formula Perpa and it calculates to 1 regardless of the time conditions.

at the end of your formula it shows J1 I2) is there an argument missing there i should know about?

 

[Simmo1313]

Try this in cell J2 (you can then copy it down and across)

=IF($B2=1,IF($H2<$I2,IF(AND(J$1>$H2,J$1<$I2),1,""),IF($I2<$H2,IF(OR(J$1>$H2,J$1<$I2),1,""),"Error")),"Day not 1")

Let us know if it falls short anywhere.

Ive built in some error checking, if the result is: "Day not 1", this means the day is blank, or does not have a 1 in it.
You would need to change the B2 reference to C2 for tuesday. D2 for wednesday etc.

The other error code is "error" this means either time is entered wrong or the same on and of time is used.

What do you want to show if On = 08:00 and off = 08:00?

Thanks CROY1985

When Input this formula it enters a value of 1 in every column except the one before the "on" time

 

[Simmo1313]

Thanks CROY1985

When Input this formula it enters a value of 1 in every column except the one before the "on" time

For example when I input time on at 7pm and off at 7am it inputs a value of 1 in every column except the time on time.

 

[Perpa]

I tried this formula Perpa and it calculates to 1 regardless of the time conditions.

at the end of your formula it shows J1 I2) is there an argument missing there i should know about?

Simmo1313,
Sorry for that, as you said it was difficult to enter the formula and have it read correctly.
Here is the formula with extra spaces...

IF ( AND ( OR ( H2 < I2 , A2 = 1 , J1 > H2 , J1 < I2 ) , ( OR ( H2 > I2 , A2 = 1 , J1 < H2 , J1 > I2 ) ) ) , 1 , 0 )

Perpa

 

[Simmo1313]

Simmo1313,
Sorry for that, as you said it was difficult to enter the formula and have it read correctly.
Here is the formula with extra spaces...

IF ( AND ( OR ( H2 < I2 , A2 = 1 , J1 > H2 , J1 < I2 ) , ( OR ( H2 > I2 , A2 = 1 , J1 < H2 , J1 > I2 ) ) ) , 1 , 0 )

Perpa

I still get that returning a value of 1 in every column.

 

[Perpa]

I still get that returning a value of 1 in every column.

Simmo1313,
There is something you may not be aware that is going on with your times, ie. '7pm, 2am'.. & etc.
Excel doesn't see those as 'times', Excel thinks they are 'text'. Try this in two cells below your work
range...Enter '7am' in one cell, then enter '7 am' (notice the space between the '7' and 'am' in the
second cell. Excel immediately converts '7 am' to '7:00 AM'. Notice also that the first cell contents
stay to the left of the cell (because it is text), whereas the second cells contents shift to the right
(because it is numbers/time). I have encountered this before and it took me a little while to
remember what I did that time. The other thing that complicates times is at midnight and noon.
Midnight is '12:00 AM' whereas noon is '12:00 PM'. For your lighting audit, you might find it easier
to give your times a 24-hour format, ie. '1:00 PM' becomes '13:00' and on.

Yesterday was a travel day for me so I wasn't able to work on this. I'll have another look at it with
the changes I have described above, and get back to you if/when I come up with something that will
work.
Perpa

 

[Simmo1313]

Simmo1313,
There is something you may not be aware that is going on with your times, ie. '7pm, 2am'.. & etc.
Excel doesn't see those as 'times', Excel thinks they are 'text'. Try this in two cells below your work
range...Enter '7am' in one cell, then enter '7 am' (notice the space between the '7' and 'am' in the
second cell. Excel immediately converts '7 am' to '7:00 AM'. Notice also that the first cell contents
stay to the left of the cell (because it is text), whereas the second cells contents shift to the right
(because it is numbers/time). I have encountered this before and it took me a little while to
remember what I did that time. The other thing that complicates times is at midnight and noon.
Midnight is '12:00 AM' whereas noon is '12:00 PM'. For your lighting audit, you might find it easier
to give your times a 24-hour format, ie. '1:00 PM' becomes '13:00' and on.

Yesterday was a travel day for me so I wasn't able to work on this. I'll have another look at it with
the changes I have described above, and get back to you if/when I come up with something that will
work.
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....