Looking for an IF formula which prints MET if less than 1 Day and MISSED if more than 1 Day

[sorngiggle]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Reporting Date[/TD]
[TD]Date Accepted[/TD]
[TD]Difference[/TD]
[TD]MET or MISSED?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]

<colgroup width="162"></colgroup> <tbody>
[TD="align: right"] 11/07/18 11:50 [/TD]

</tbody>

[/TD]
[TD]

<colgroup width="200"></colgroup> <tbody>
[TD="align: right"] 11/07/18 11:51 [/TD]

</tbody>

[/TD]
[TD]

<colgroup width="240"></colgroup> <tbody>
[TD="align: left"] 0 days, 0 hrs, 1 mins [/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]

<colgroup width="162"></colgroup> <tbody>
[TD="align: right"] 26/07/18 08:06 [/TD]

</tbody>

[/TD]
[TD]

<colgroup width="200"></colgroup> <tbody>
[TD="align: right"] 27/07/18 10:14 [/TD]

</tbody>

[/TD]
[TD]

<colgroup width="240"></colgroup> <tbody>
[TD="align: left"] 1 days, 2 hrs, 8 mins [/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]

<colgroup width="162"></colgroup> <tbody>
[TD="align: right"] 01/07/18 21:16 [/TD]

</tbody>

[/TD]
[TD]

<colgroup width="221"></colgroup> <tbody>
[TD="align: right"] 02/07/18 15:19 [/TD]

</tbody>

[/TD]
[TD]

<colgroup width="240"></colgroup> <tbody>
[TD="align: left"] 0 days, 12 hrs, 3 mins [/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So, i used the formula

=INT(B2-A2) & " days, " & HOUR(B2-A2) & " hrs, " & MINUTE(B2-A2) & " mins "

for the Difference between B2 and A2

Now, i want the D column to automatically detect if Column C is > 1 day it prints "Missed" and if it less < 1 day it prints "Met"

I tried using

=IF(C2>=TIME(23,59,59)+ TIME(0,0,1),"MISSED","MET")

it gave me a wrong answer. D2 should have been MET but it shows MISSED.

Thanks,
Anusorn Khurana

 

[Peter_SSs]

Try
=IF(B2-A2>1,"Missed","Met")

 

[sorngiggle]

Try
=IF(B2-A2>1,"Missed","Met")

Worked Perfectly. Thank You!

Can I Pick Your Brain A Bit More?
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Reporting Date[/TD]
[TD]Date Accepted[/TD]
[TD]STATUS[/TD]
[TD]Differece[/TD]
[TD]MET or MISSED?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11/07/18 11:50[/TD]
[TD]11/07/18 11:51[/TD]
[TD]1[/TD]
[TD]0 days, 0 hrs, 1 mins[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]26/07/18 08:06[/TD]
[TD]27/07/18 10:14[/TD]
[TD]0[/TD]
[TD]1 days, 2 hrs, 8 mins[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]01/07/18 21:16[/TD]
[TD]02/07/18 15:19[/TD]
[TD]1[/TD]
[TD]0 days, 12 hrs, 3 mins[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If i wanted to use an IF statement

to Let it first check if STATUS = 1 or 0
If STATUS (C2) = 1 then check if DIfference (D2) is > 1 day it prints "MISSED" and if it is < 1 it prints "MET"
If STATUS (C2) = 0 then check if DIfference (D2) is > 3 days it prints "MISSED" and if it is < 3 it prints "MET"

 

[Peter_SSs]

Worked Perfectly. Thank You!

Great!

Can I Pick Your Brain A Bit More?
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Reporting Date[/TD]
[TD]Date Accepted[/TD]
[TD]STATUS[/TD]
[TD]Differece[/TD]
[TD]MET or MISSED?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11/07/18 11:50[/TD]
[TD]11/07/18 11:51[/TD]
[TD]1[/TD]
[TD]0 days, 0 hrs, 1 mins[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]26/07/18 08:06[/TD]
[TD]27/07/18 10:14[/TD]
[TD]0[/TD]
[TD]1 days, 2 hrs, 8 mins[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]01/07/18 21:16[/TD]
[TD]02/07/18 15:19[/TD]
[TD]1[/TD]
[TD]0 days, 12 hrs, 3 mins[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If i wanted to use an IF statement

to Let it first check if STATUS = 1 or 0
If STATUS (C2) = 1 then check if DIfference (D2) is > 1 day it prints "MISSED" and if it is < 1 it prints "MET"
If STATUS (C2) = 0 then check if DIfference (D2) is > 3 days it prints "MISSED" and if it is < 3 it prints "MET"

Does this do what you want?
=IF(B2-A2>3-2*C2,"Missed","Met")

 

[sorngiggle]

It Works!. But I am kind of new here. If you dont mind, could you explain the statement in plain english, so i can understand it and perhaps be able to use it in the future.
Thanks again!

 

[sorngiggle]

Great!

Does this do what you want?
=IF(B2-A2>3-2*C2,"Missed","Met")

It works perfectly! If you dont mind, could you please explain to me in plain english how the code works. Maybe i could use it in the future.

Thanks alot

 

[Peter_SSs]

It works perfectly!

Great!

If you dont mind, could you please explain to me in plain english how the code works. Maybe i could use it in the future.

The critical part is blue
=IF(B2-A2>3-2*C2,"Missed","Met")

When the Status column (C) is 1, this becomes
3-2*1
3-2
1
So if the difference is > 1 "Missed" etc

When the Status column is 0, this becomes
3-2*0
3-0
3
So if the difference is > 3 "Missed" etc

Another way of writing the formula that might be simpler to follow, would be
=IF(B2-A2>IF(C2=1,1,3),"Missed","Met")

 

[sorngiggle]

Great!


The critical part is blue
=IF(B2-A2>3-2*C2,"Missed","Met")

When the Status column (C) is 1, this becomes
3-2*1
3-2
1
So if the difference is > 1 "Missed" etc

When the Status column is 0, this becomes
3-2*0
3-0
3
So if the difference is > 3 "Missed" etc

Another way of writing the formula that might be simpler to follow, would be
=IF(B2-A2>IF(C2=1,1,3),"Missed","Met")

Thanks alot, Really appreciate it

 

[Peter_SSs]

Thanks alot, Really appreciate it

No problem. Cheers.