ExcelBanter

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - If (https://www.excelbanter.com/excel-discussion-misc-queries/222222-if.html)

Hi, i have this code:

=IF(AND(F5="COS",B5--"11:00:00"),IF(K5<B5,"Late","Early")&TEXT(ABS( K5-B5),"hh:mm:ss"),IF(OR(F5="ACE",AND(F5="COS",B5<=--"11:00:00")),IF(J5<B5,"Late","Early")&TEXT(ABS (J5-B5),"hh:mm:ss"),""))

Can i make the code something like :

=IF(AND(F5="COS",B5--"11:00:00"),IF(K5<B5,"Late","Early")&TEXT(ABS( K5-B5),"hh:mm:ss"),IF(OR(F5="ACE",AND(F5="COS",B5<=--"11:00:00")),IF(J5<B5,"Late","Early")&TEXT(ABS (J5-B5),"hh:mm:ss"),""))
and
IF(AND(F5="ACE",B5--"11:00:00"),IF(K5<B5,"Late","Early")&TEXT(ABS( K5-B5),"hh:mm:ss"),IF(OR(F5="COS",AND(F5="ACE",B5<=--"11:00:00")),IF(J5<B5,"Late","Early")&TEXT(ABS (J5-B5),"hh:mm:ss"),""))

Thanks.

=IF(AND(OR(F5="COS",F5="ACE"),B5--"11:00:00"),IF(K5<B5,"Late","Early")&TEXT(ABS( K5-B5),"hh:mm:ss"),IF(OR(F5="ACE",AND(F5="COS",B5<=--"11:00:00")),IF(J5<B5,"Late","Early")&TEXT(ABS (J5-B5),"hh:mm:ss"),""))

--
__________________________________
HTH

Bob

"puiuluipui" wrote in message
...
Hi, i have this code:

=IF(AND(F5="COS",B5--"11:00:00"),IF(K5<B5,"Late","Early")&TEXT(ABS( K5-B5),"hh:mm:ss"),IF(OR(F5="ACE",AND(F5="COS",B5<=--"11:00:00")),IF(J5<B5,"Late","Early")&TEXT(ABS (J5-B5),"hh:mm:ss"),""))

Can i make the code something like :

=IF(AND(F5="COS",B5--"11:00:00"),IF(K5<B5,"Late","Early")&TEXT(ABS( K5-B5),"hh:mm:ss"),IF(OR(F5="ACE",AND(F5="COS",B5<=--"11:00:00")),IF(J5<B5,"Late","Early")&TEXT(ABS (J5-B5),"hh:mm:ss"),""))
and
IF(AND(F5="ACE",B5--"11:00:00"),IF(K5<B5,"Late","Early")&TEXT(ABS( K5-B5),"hh:mm:ss"),IF(OR(F5="COS",AND(F5="ACE",B5<=--"11:00:00")),IF(J5<B5,"Late","Early")&TEXT(ABS (J5-B5),"hh:mm:ss"),""))

Thanks.

Hi, i need the code a little more complex. My question was not correct. Sorry
for that. I will try to explain better. This is the "history" of what i need.

I first had this code:

=IF(F5="COS",IF(J5<B5,"Late ","Early
")&TEXT(ABS(J5-B5),"hh:mm:ss"),IF(F5="ACE",IF(L5<B5,"Late ","Early
")&TEXT(ABS(L5-B5),"hh:mm:ss"),""))

After that, i need it like this:...

Only "COS" to display the result by time
If it's less than 11:00:00 the reference to be "J5"
If it's bigger than 11:00:00 the reference to be "K5"

And i had this code:

=IF(AND(F5="COSMETICE 1",B5 --"11:00:00"),IF(K5<B5,"Late
","Early")&TEXT(ABS(K5-B5),"hh:mm:ss"),IF(OR(F5="ACE",AND(F5="COSMETICE
1",B5<=--"11:00:00")),IF(J5<B5,"Late ","Early
")&TEXT(ABS(J5-B5),"hh:mm:ss"),""))

Finally, i need :...

"cos" and "cos 1" to have reference from "J5" and "K5" and to display result
by time.
and
"ace", "van", and "ach" to have reference from "L5". (i don't want the
code to display time)

I hope i wrote everything corectly. And sorry for this long story but i
wanted to explain better because this code will help me allot.

Thanks for your first code and i hope you can help me with this one too.

Thanks in advance.

Bob Phillips" wrote:

=IF(AND(OR(F5="COS",F5="ACE"),B5--"11:00:00"),IF(K5<B5,"Late","Early")&TEXT(ABS( K5-B5),"hh:mm:ss"),IF(OR(F5="ACE",AND(F5="COS",B5<=--"11:00:00")),IF(J5<B5,"Late","Early")&TEXT(ABS (J5-B5),"hh:mm:ss"),""))

--
__________________________________
HTH

Bob

"puiuluipui" wrote in message
...
Hi, i have this code:

=IF(AND(F5="COS",B5--"11:00:00"),IF(K5<B5,"Late","Early")&TEXT(ABS( K5-B5),"hh:mm:ss"),IF(OR(F5="ACE",AND(F5="COS",B5<=--"11:00:00")),IF(J5<B5,"Late","Early")&TEXT(ABS (J5-B5),"hh:mm:ss"),""))

Can i make the code something like :

=IF(AND(F5="COS",B5--"11:00:00"),IF(K5<B5,"Late","Early")&TEXT(ABS( K5-B5),"hh:mm:ss"),IF(OR(F5="ACE",AND(F5="COS",B5<=--"11:00:00")),IF(J5<B5,"Late","Early")&TEXT(ABS (J5-B5),"hh:mm:ss"),""))
and
IF(AND(F5="ACE",B5--"11:00:00"),IF(K5<B5,"Late","Early")&TEXT(ABS( K5-B5),"hh:mm:ss"),IF(OR(F5="COS",AND(F5="ACE",B5<=--"11:00:00")),IF(J5<B5,"Late","Early")&TEXT(ABS (J5-B5),"hh:mm:ss"),""))

Thanks.

Sorry, you have totally lost me.

--
__________________________________
HTH

Bob

"puiuluipui" wrote in message
...
Hi, i need the code a little more complex. My question was not correct.
Sorry
for that. I will try to explain better. This is the "history" of what i
need.

I first had this code:

=IF(F5="COS",IF(J5<B5,"Late ","Early
")&TEXT(ABS(J5-B5),"hh:mm:ss"),IF(F5="ACE",IF(L5<B5,"Late ","Early
")&TEXT(ABS(L5-B5),"hh:mm:ss"),""))

After that, i need it like this:...

Only "COS" to display the result by time
If it's less than 11:00:00 the reference to be "J5"
If it's bigger than 11:00:00 the reference to be "K5"

And i had this code:

=IF(AND(F5="COSMETICE 1",B5 --"11:00:00"),IF(K5<B5,"Late
","Early")&TEXT(ABS(K5-B5),"hh:mm:ss"),IF(OR(F5="ACE",AND(F5="COSMETICE
1",B5<=--"11:00:00")),IF(J5<B5,"Late ","Early
")&TEXT(ABS(J5-B5),"hh:mm:ss"),""))

Finally, i need :...

"cos" and "cos 1" to have reference from "J5" and "K5" and to display
result
by time.
and
"ace", "van", and "ach" to have reference from "L5". (i don't want the
code to display time)

I hope i wrote everything corectly. And sorry for this long story but i
wanted to explain better because this code will help me allot.

Thanks for your first code and i hope you can help me with this one too.

Thanks in advance.

Bob Phillips" wrote:

=IF(AND(OR(F5="COS",F5="ACE"),B5--"11:00:00"),IF(K5<B5,"Late","Early")&TEXT(ABS( K5-B5),"hh:mm:ss"),IF(OR(F5="ACE",AND(F5="COS",B5<=--"11:00:00")),IF(J5<B5,"Late","Early")&TEXT(ABS (J5-B5),"hh:mm:ss"),""))

--
__________________________________
HTH

Bob

"puiuluipui" wrote in message
...
Hi, i have this code:

=IF(AND(F5="COS",B5--"11:00:00"),IF(K5<B5,"Late","Early")&TEXT(ABS( K5-B5),"hh:mm:ss"),IF(OR(F5="ACE",AND(F5="COS",B5<=--"11:00:00")),IF(J5<B5,"Late","Early")&TEXT(ABS (J5-B5),"hh:mm:ss"),""))

Can i make the code something like :

=IF(AND(F5="COS",B5--"11:00:00"),IF(K5<B5,"Late","Early")&TEXT(ABS( K5-B5),"hh:mm:ss"),IF(OR(F5="ACE",AND(F5="COS",B5<=--"11:00:00")),IF(J5<B5,"Late","Early")&TEXT(ABS (J5-B5),"hh:mm:ss"),""))
and
IF(AND(F5="ACE",B5--"11:00:00"),IF(K5<B5,"Late","Early")&TEXT(ABS( K5-B5),"hh:mm:ss"),IF(OR(F5="COS",AND(F5="ACE",B5<=--"11:00:00")),IF(J5<B5,"Late","Early")&TEXT(ABS (J5-B5),"hh:mm:ss"),""))

Thanks.

Sorry about that. I just need the code and i was not so clear.
I have something like this:

A B .... F G ..... J K
L

date 11:55:00 cos (result) 08:00:00 12:30:00 08:30:00
date 07:45:10 cos 1 (result) 08:00:00 12:30:00 08:30:00
date 08:30:04 ace (result) 08:00:00 12:30:00 08:30:00
date 08:29:23 van (result) 08:00:00 12:30:00 08:30:00
date 08:15:00 ach (result) 08:00:00 12:30:00 08:30:00

I need the code in "G"

If "F1"="cos" and B1" is bigger than 11:00:00 than the code reference to be
"K1"
If "F2"="cos 1" and B2" is smaller than 11:00:00 than the code reference to
be "J2"

If "F3"="ace" than the code reference to be "L3"
If "F4"="van" than the code reference to be "L4"
If "F5"="ach" than the code reference to be "L5"

The code to make the diference between "B column" and "F reference" with
"late" and "early" like in example below:

"date 11:55:00 cos (early 00:35:00) 08:00:00 12:30:00 08:30:00"
"date 08:15:00 ach (early 00:15:00) 08:00:00 12:30:00 08:30:00"

Thanks in advance

"Bob Phillips" a scris:

Sorry, you have totally lost me.

--
__________________________________
HTH

Bob

"puiuluipui" wrote in message
...
Hi, i need the code a little more complex. My question was not correct.
Sorry
for that. I will try to explain better. This is the "history" of what i
need.

I first had this code:

=IF(F5="COS",IF(J5<B5,"Late ","Early
")&TEXT(ABS(J5-B5),"hh:mm:ss"),IF(F5="ACE",IF(L5<B5,"Late ","Early
")&TEXT(ABS(L5-B5),"hh:mm:ss"),""))

After that, i need it like this:...

Only "COS" to display the result by time
If it's less than 11:00:00 the reference to be "J5"
If it's bigger than 11:00:00 the reference to be "K5"

And i had this code:

=IF(AND(F5="COSMETICE 1",B5 --"11:00:00"),IF(K5<B5,"Late
","Early")&TEXT(ABS(K5-B5),"hh:mm:ss"),IF(OR(F5="ACE",AND(F5="COSMETICE
1",B5<=--"11:00:00")),IF(J5<B5,"Late ","Early
")&TEXT(ABS(J5-B5),"hh:mm:ss"),""))

Finally, i need :...

"cos" and "cos 1" to have reference from "J5" and "K5" and to display
result
by time.
and
"ace", "van", and "ach" to have reference from "L5". (i don't want the
code to display time)

I hope i wrote everything corectly. And sorry for this long story but i
wanted to explain better because this code will help me allot.

Thanks for your first code and i hope you can help me with this one too.

Thanks in advance.

Bob Phillips" wrote:

=IF(AND(OR(F5="COS",F5="ACE"),B5--"11:00:00"),IF(K5<B5,"Late","Early")&TEXT(ABS( K5-B5),"hh:mm:ss"),IF(OR(F5="ACE",AND(F5="COS",B5<=--"11:00:00")),IF(J5<B5,"Late","Early")&TEXT(ABS (J5-B5),"hh:mm:ss"),""))

--
__________________________________
HTH

Bob

"puiuluipui" wrote in message
...
Hi, i have this code:

=IF(AND(F5="COS",B5--"11:00:00"),IF(K5<B5,"Late","Early")&TEXT(ABS( K5-B5),"hh:mm:ss"),IF(OR(F5="ACE",AND(F5="COS",B5<=--"11:00:00")),IF(J5<B5,"Late","Early")&TEXT(ABS (J5-B5),"hh:mm:ss"),""))

Can i make the code something like :

=IF(AND(F5="COS",B5--"11:00:00"),IF(K5<B5,"Late","Early")&TEXT(ABS( K5-B5),"hh:mm:ss"),IF(OR(F5="ACE",AND(F5="COS",B5<=--"11:00:00")),IF(J5<B5,"Late","Early")&TEXT(ABS (J5-B5),"hh:mm:ss"),""))
and
IF(AND(F5="ACE",B5--"11:00:00"),IF(K5<B5,"Late","Early")&TEXT(ABS( K5-B5),"hh:mm:ss"),IF(OR(F5="COS",AND(F5="ACE",B5<=--"11:00:00")),IF(J5<B5,"Late","Early")&TEXT(ABS (J5-B5),"hh:mm:ss"),""))

Thanks.