VIP

Trusted Members

December 7, 2016

VIP

Trusted Members

December 7, 2016

December 8, 2017

Hello Anders;

Yes, there is Progress with IF formula.

In workbook attached, in Column H, there is

=IF(AND(OR(D8="Web",D8 = "Email"),AND(G8<=17,G8>17)),"Work","Home") for hours greater than 17 and it gives me correct

output which is home , and for

Work output there is =IF(AND(OR(D9="Web",D9 = "Email"),OR(G9<=17,G9>17)),"Work","Home"), so I like to learn can this

formula be same as "home" output formula ? I tried few times, but cannot figure it out. Can you Please help me ?

Thank you very much.

Have a great day.

VIP

Trusted Members

December 7, 2016

Hello Mitul,

I don't understand why you included column D in your formula.

The question is: "Do our customers shop at work (most likely between 8:00 and 17:00) or at home?"

To answer that question you only need to know at what time the order was made, not how.

One other thing that needs be pointed out here is how to work with date and time.

I don't know who have created a custom number format for the time data in column G, but even though the time in

column G is shown as hours by using custom number format, it does not mean that the value in the cells are just

in hours. As I try to show in attached file, in columns I, J and K I have added some extra controls.

In I8 I am checking if the data in G8 is equal to 22 or not, using following formula:

=IF(G8=22,"G8 is 22","G8 is not 22")

As you can see, the result is that the data in cell G8 is not equal to 22, even though 22 is shown.

In J8 I have take the time value from cell G8 and convert it to just hours, using following formula:

=HOUR(G8)

As you can see, the value shown is 22.

In K8 I run the same check as I did in I8, just checking the value in cell J8 instead.

=IF(J8=22,"J8 is 22","J8 is not 22")

And as you can see, I now get that the value in cell J8 is equal to 22.

In following cells down to row 14 I am doing another check.

In cells I9 to I14 I am checking if the values in cells G9 to G14 is greater than (gt) or less than (lt) 17.

=IF(G9>17,"G"&ROW(G9)&" is gt 17","G"&ROW(G9)&" is lt 17")

The result for all cells is that all the data is less than 17, even though one row is showing 22.

In cells K9 to K14 I do the same check but now against the cells in column J, which shows the hours.

=IF(J9>17,"J"&ROW(G9)&" is gt 17","J"&ROW(J9)&" is lt 17")

And now we see that in cell K14 it is now showing that the value in cell J14 is greater than 17.

What I am trying to point out here is that you need to ensure that you work with correct data.

A hint, when you have correct hour data, check if the hour is less than 8 OR greater than 17 (you can of course

check if the hour is greater than 7 and less than 18). If you don't want to create an extra hour column, you can

of course have the hour conversion within your formula.

Good luck.

/Anders

1 Guest(s)