IF question

[puiuluipui]

Hi, i have this code:
=IF(M5<B5,"Late ","Early ")&TEXT(ABS(M5-B5),"hh:mm:ss")

I need it a little more complex.

I need the code to look in F5 and if the code find "COS" to know that the
reference is in M5. If the code find (ACE) to know that the reference in in
L5. After that, the code to look in B5. If the time is smaller than M5 or L5,
the result to be "early". If the time is bigger, than the result to display
"late".
Can this be done?
Thanks in advance.

 

[Max]

One try:
=IF(F5="COS",IF(M5<B5,"Late ","Early ")&TEXT(ABS(M5-B5),"hh:mm:ss"),
IF(F5="ACE",IF(L5<B5,"Late ","Early ")&TEXT(ABS(L5-B5),"hh:mm:ss"),""))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[puiuluipui]

It's working, but i have one more question. In B5 with "COS" , the code can
sometimes find 08:00:00 and 12:30:00, so the reference can be either M5 or L5
Can the code, only in "cos", display the result by the time? The same thing
with "late" and "early".

If it's less than 11:00:00 the reference to be "L5"
If it's bigger than 11:00:00 the reference to be "M5"

Thanks in advance.


"Max" a scris:

 

[Max]

It's working ...
Welcome, but pl mark that response by clicking the YES button there

but i have one more question ..

Maybe this:
=IF(AND(F5="COS",B5> --"11:00:00"),IF(M5<B5,"Late ","Early
")&TEXT(ABS(M5-B5),"hh:mm:ss"),IF(OR(F5="ACE",AND(F5="COS",B5<=
--"11:00:00")),IF(L5<B5,"Late ","Early ")&TEXT(ABS(L5-B5),"hh:mm:ss"),""))

If above helps, pl mark this response as well
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
--

 

[puiuluipui]

I allways do click on yes.
Can you help me with the last question?
Thanks allot.

"Max" a scris:

 

[Max]

Can you help me with the last question?
um, perhaps you missed my venture for you in my last response?
reproduced below:

but i have one more question ..

Maybe this:
=IF(AND(F5="COS",B5> --"11:00:00"),IF(M5<B5,"Late ","Early
")&TEXT(ABS(M5-B5),"hh:mm:ss"),IF(OR(F5="ACE",AND(F5="COS",B5<=
--"11:00:00")),IF(L5<B5,"Late ","Early ")&TEXT(ABS(L5-B5),"hh:mm:ss"),""))

If above helps, pl mark this response as well
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

 

[puiuluipui]

Yes, i missed that. Works great. Thanks allot.
You are great. Thanks.

"Max" a scris:

 

[Max]

Yes, i missed that. Works great. Thanks a lot.
No prob. Glad it worked out ok for you.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

 

[puiuluipui]

Hi Max, i have one more problem. In your last code i frogot to tell you that
"ACE" reference is in "J5".
Your code take references for "COS" (before and after 11:00:00) from "M5"
and "L5".
But in the same code i need "ACE" to have reference in "J5"

Thanks in advance.

"Max" a scris:

 

[Max]

Pl start a new thread in future. This thread is long closed.
I'm not sure, is it just a simple matter of replacing M5 by J5 in the
expression ?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik

 

[Max]

My apologies, pl disregard the comment:

.. is it just a simple matter of replacing M5 by J5 in the expression ?

Suggest you start a new thread for your new query.
In your new thread, paste your expression that needs amendment.
Make it easy for any responder to help you.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---

 

[puiuluipui]

Hi Max, i didn't want to bother you, but it didn't seamed fair to me to talk
to somebody about your code.

I replaced M5 with J5 but it works only for one situation.
Your first code works for both situation ( "COS", "ACE" ) in the same code.
Your second code works only for one situation at the time. I need one code
for both situations.
-for "COS" with time (11:00:00) and two reference (M5 and L5)
-for "ACE" with only one reference (J5) and no time.

The second code works so good to me, but only if i have tho codes in one. it
will take me allot of time to change the code for every situation.

Actually i have more than ("COS" and "ACE"), i have ("COS" and "COS 1, and
"ACE", "VAN" and "ACH") but i thought i will change the code for the rest off
them.
I just need one code for boths group situations. (COS and COS 1 )- [ref
"M5","L5"] with time, and (ACE, VAN and ACH )-[ref J5]


Thanks again and sorry if i bother you so much, but you are the only one who
helped me with this.
Thanks.

 

[Max]

.. but it didn't seem fair to me to talk to somebody about your code ..
Everything which is posted/discussed in the newsgroups is public domain.
Meant to be like that, for the benefit of all readers. Pl feel free to use
it or if you want to discuss further, start new thread(s). That's the way.

.. but you are the only one who helped me with this ..

But .. I may no longer be able / be around to help you further. Then what
are you going to do? It's just normal advice. Nothing discourteous. Do start
new threads for new queries. Lots of responders out here in the excel
newsgroups who can help you. All the best.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---