Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If
=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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
If
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|