IF function
 

I want to use the IF function for working out the time difference between two
cells eg
if planned time was A= 09:00 and actual time B=11:00
C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B
A+3Hr

IF function
 

Since you didn't provide values for in-between values, I'll assume you want
to round, or it's not an issue.

=CHOOSE(ROUND((B2-A2)*24+1,0),4,3,2,1)

You might like to change the ROUND to INT or ROUNDUP, depending on your
situation. Thankfully, both of those use the exact same arguements.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Graham" wrote:

I want to use the IF function for working out the time difference between two
cells eg
if planned time was A= 09:00 and actual time B=11:00
C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B
A+3Hr

IF function
 

Try,

=LOOKUP(TEXT(B1-A1,"h"),{"0","1","2","3"},{4,3,2,1})

Mike

"Graham" wrote:

I want to use the IF function for working out the time difference between two
cells eg
if planned time was A= 09:00 and actual time B=11:00
C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B
A+3Hr

IF function
 

Hi Luke
Thanks,however
How do I incorporate the IF argument base on the senario below where the
argument is based on Cell 'C' which is the difference between 'B'-'A'
C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B
A+3Hr

"Luke M" wrote:

Since you didn't provide values for in-between values, I'll assume you want
to round, or it's not an issue.

=CHOOSE(ROUND((B2-A2)*24+1,0),4,3,2,1)

You might like to change the ROUND to INT or ROUNDUP, depending on your
situation. Thankfully, both of those use the exact same arguements.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Graham" wrote:

I want to use the IF function for working out the time difference between two
cells eg
if planned time was A= 09:00 and actual time B=11:00
C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B
A+3Hr

IF function
 

Graham,

You put Luke's (or my) formula in column c and drag down as required.

Mike

"Graham" wrote:

Hi Luke
Thanks,however
How do I incorporate the IF argument base on the senario below where the
argument is based on Cell 'C' which is the difference between 'B'-'A'
C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B
A+3Hr

"Luke M" wrote:

Since you didn't provide values for in-between values, I'll assume you want
to round, or it's not an issue.

=CHOOSE(ROUND((B2-A2)*24+1,0),4,3,2,1)

You might like to change the ROUND to INT or ROUNDUP, depending on your
situation. Thankfully, both of those use the exact same arguements.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Graham" wrote:

I want to use the IF function for working out the time difference between two
cells eg
if planned time was A= 09:00 and actual time B=11:00
C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B
A+3Hr

IF function
 

Thanks Mike, worked beautifully, If i need any other help I will definately
contact you guys
Regards

"Mike H" wrote:

Graham,

You put Luke's (or my) formula in column c and drag down as required.

Mike

"Graham" wrote:

Hi Luke
Thanks,however
How do I incorporate the IF argument base on the senario below where the
argument is based on Cell 'C' which is the difference between 'B'-'A'
C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B
A+3Hr

"Luke M" wrote:

Since you didn't provide values for in-between values, I'll assume you want
to round, or it's not an issue.

=CHOOSE(ROUND((B2-A2)*24+1,0),4,3,2,1)

You might like to change the ROUND to INT or ROUNDUP, depending on your
situation. Thankfully, both of those use the exact same arguements.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Graham" wrote:

I want to use the IF function for working out the time difference between two
cells eg
if planned time was A= 09:00 and actual time B=11:00
C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B
A+3Hr

IF function
 

Hi Mike I tried what you said and it worked great , however what happens when
(B-A) is -1 or -2 etc, Here I need the Lookup to return 4 always if the
diifference is <=0

"Mike H" wrote:

Graham,

You put Luke's (or my) formula in column c and drag down as required.

Mike

"Graham" wrote:

Hi Luke
Thanks,however
How do I incorporate the IF argument base on the senario below where the
argument is based on Cell 'C' which is the difference between 'B'-'A'
C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B
A+3Hr

"Luke M" wrote:

Since you didn't provide values for in-between values, I'll assume you want
to round, or it's not an issue.

=CHOOSE(ROUND((B2-A2)*24+1,0),4,3,2,1)

You might like to change the ROUND to INT or ROUNDUP, depending on your
situation. Thankfully, both of those use the exact same arguements.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Graham" wrote:

I want to use the IF function for working out the time difference between two
cells eg
if planned time was A= 09:00 and actual time B=11:00
C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B
A+3Hr

IF function
 

"Graham" wrote:

Hi Mike I tried what you said and it worked great , however what happens when
(B-A) is -1 or -2 etc, Here I need the Lookup to return 4 always if the
diifference is <=0,

"Mike H" wrote:

Graham,

You put Luke's (or my) formula in column c and drag down as required.

Mike

"Graham" wrote:

Hi Luke
Thanks,however
How do I incorporate the IF argument base on the senario below where the
argument is based on Cell 'C' which is the difference between 'B'-'A'
C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B
A+3Hr

"Luke M" wrote:

Since you didn't provide values for in-between values, I'll assume you want
to round, or it's not an issue.

=CHOOSE(ROUND((B2-A2)*24+1,0),4,3,2,1)

You might like to change the ROUND to INT or ROUNDUP, depending on your
situation. Thankfully, both of those use the exact same arguements.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Graham" wrote:

I want to use the IF function for working out the time difference between two
cells eg
if planned time was A= 09:00 and actual time B=11:00
C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B
A+3Hr