What's sintaxe or formula, please
 

Hi!

I have:
Time in A2, text in B2, another time in C2 and another text in D2.
In A and C columns, the times are incremented one minute.
In B column the text is fixed. In C column the text is variable.

In Conditionall Format in D2 cell I use the next formulae:
=IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0)
to write D2 red if condition is true, and work fine, row by row.

Now, I would like change formulae to work with a block, for example A2:D100,
but I don't know the correct sintax.
For another words:

A B C D
2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the interval
time between A2 and C2 is < 00:05)
3 09:01 Text2 09:01 Text10 (D3 don't red)
4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the interval
time is < 00:05 between C5 and A5 both with Text1)
5 09:03 Text4 09:03 Text12
6 09:04 Text6 09:04 ...
7 09:05 Text7 09:05
8 09:06 Text8 09:06
9 09:07 Text9 09:07 Text1 (Don't write red because the interval time
is 00:05:00 between C9 and A2 both with Text1)
....

I thought about
=IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF(C2:C100=A2:A100+"00:05:00";1;0) );0)

But don't work.
I woul like any idea.
Thanks in advance.
an

What's sintaxe or formula, please
 

Why is 4 red but 3 isn't?

Do you want them red if all rows match?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
Hi!

I have:
Time in A2, text in B2, another time in C2 and another text in D2.
In A and C columns, the times are incremented one minute.
In B column the text is fixed. In C column the text is variable.

In Conditionall Format in D2 cell I use the next formulae:
=IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0)
to write D2 red if condition is true, and work fine, row by row.

Now, I would like change formulae to work with a block, for example
A2:D100,
but I don't know the correct sintax.
For another words:

A B C D
2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the interval
time between A2 and C2 is < 00:05)
3 09:01 Text2 09:01 Text10 (D3 don't red)
4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the interval
time is < 00:05 between C5 and A5 both with Text1)
5 09:03 Text4 09:03 Text12
6 09:04 Text6 09:04 ...
7 09:05 Text7 09:05
8 09:06 Text8 09:06
9 09:07 Text9 09:07 Text1 (Don't write red because the interval
time
is 00:05:00 between C9 and A2 both with Text1)
...

I thought about

=IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF
(C2:C100=A2:A100+"00:05:00";1;0));0)

But don't work.
I woul like any idea.
Thanks in advance.
an

What's sintaxe or formula, please
 

Sorry for my delay.

3 isn't red because D4<B4 (Text11<Text3)
of where, it does not interest the hour.
It only interests. T write red if two equal texts will be in an interval < 5
min

Thanks.an

"Bob Phillips" wrote:

Why is 4 red but 3 isn't?

Do you want them red if all rows match?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
Hi!

I have:
Time in A2, text in B2, another time in C2 and another text in D2.
In A and C columns, the times are incremented one minute.
In B column the text is fixed. In C column the text is variable.

In Conditionall Format in D2 cell I use the next formulae:
=IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0)
to write D2 red if condition is true, and work fine, row by row.

Now, I would like change formulae to work with a block, for example
A2:D100,
but I don't know the correct sintax.
For another words:

A B C D
2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the interval
time between A2 and C2 is < 00:05)
3 09:01 Text2 09:01 Text10 (D3 don't red)
4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the interval
time is < 00:05 between C5 and A5 both with Text1)
5 09:03 Text4 09:03 Text12
6 09:04 Text6 09:04 ...
7 09:05 Text7 09:05
8 09:06 Text8 09:06
9 09:07 Text9 09:07 Text1 (Don't write red because the interval
time
is 00:05:00 between C9 and A2 both with Text1)
...

I thought about

=IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF
(C2:C100=A2:A100+"00:05:00";1;0));0)

But don't work.
I woul like any idea.
Thanks in advance.
an

What's sintaxe or formula, please
 

3 isn't red because D4<B4 (Text11<Text3)
of where, it does not interest the hour.
It only interests write red if two equal texts will be in an interval < 5 min

Thanks.
an

"Bob Phillips" wrote:

Why is 4 red but 3 isn't?

Do you want them red if all rows match?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
Hi!

I have:
Time in A2, text in B2, another time in C2 and another text in D2.
In A and C columns, the times are incremented one minute.
In B column the text is fixed. In C column the text is variable.

In Conditionall Format in D2 cell I use the next formulae:
=IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0)
to write D2 red if condition is true, and work fine, row by row.

Now, I would like change formulae to work with a block, for example
A2:D100,
but I don't know the correct sintax.
For another words:

A B C D
2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the interval
time between A2 and C2 is < 00:05)
3 09:01 Text2 09:01 Text10 (D3 don't red)
4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the interval
time is < 00:05 between C5 and A5 both with Text1)
5 09:03 Text4 09:03 Text12
6 09:04 Text6 09:04 ...
7 09:05 Text7 09:05
8 09:06 Text8 09:06
9 09:07 Text9 09:07 Text1 (Don't write red because the interval
time
is 00:05:00 between C9 and A2 both with Text1)
...

I thought about

=IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF
(C2:C100=A2:A100+"00:05:00";1;0));0)

But don't work.
I woul like any idea.
Thanks in advance.
an

What's sintaxe or formula, please
 

Sorry, I don't understand. Text11 is not in your example, and you don't
answer whether all cells must be equal in your modified function.

I guess English is not your first language. Is there not an Excel forum in
your native language?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
3 isn't red because D4<B4 (Text11<Text3)
of where, it does not interest the hour.
It only interests write red if two equal texts will be in an interval < 5
min

Thanks.
an

"Bob Phillips" wrote:

Why is 4 red but 3 isn't?

Do you want them red if all rows match?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
Hi!

I have:
Time in A2, text in B2, another time in C2 and another text in D2.
In A and C columns, the times are incremented one minute.
In B column the text is fixed. In C column the text is variable.

In Conditionall Format in D2 cell I use the next formulae:
=IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0)
to write D2 red if condition is true, and work fine, row by row.

Now, I would like change formulae to work with a block, for example
A2:D100,
but I don't know the correct sintax.
For another words:

A B C D
2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the
interval
time between A2 and C2 is < 00:05)
3 09:01 Text2 09:01 Text10 (D3 don't red)
4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the
interval
time is < 00:05 between C5 and A5 both with Text1)
5 09:03 Text4 09:03 Text12
6 09:04 Text6 09:04 ...
7 09:05 Text7 09:05
8 09:06 Text8 09:06
9 09:07 Text9 09:07 Text1 (Don't write red because the
interval
time
is 00:05:00 between C9 and A2 both with Text1)
...

I thought about


=IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF
(C2:C100=A2:A100+"00:05:00";1;0));0)

But don't work.
I woul like any idea.
Thanks in advance.
an

What's sintaxe or formula, please
 

Sorry for my English and your patience.

D3 isn't red because
A3=C3 but D3<B3

Thanks.
an

"Bob Phillips" wrote:

Sorry, I don't understand. Text11 is not in your example, and you don't
answer whether all cells must be equal in your modified function.

I guess English is not your first language. Is there not an Excel forum in
your native language?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
3 isn't red because D4<B4 (Text11<Text3)
of where, it does not interest the hour.
It only interests write red if two equal texts will be in an interval < 5
min

Thanks.
an

"Bob Phillips" wrote:

Why is 4 red but 3 isn't?

Do you want them red if all rows match?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
Hi!

I have:
Time in A2, text in B2, another time in C2 and another text in D2.
In A and C columns, the times are incremented one minute.
In B column the text is fixed. In C column the text is variable.

In Conditionall Format in D2 cell I use the next formulae:
=IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0)
to write D2 red if condition is true, and work fine, row by row.

Now, I would like change formulae to work with a block, for example
A2:D100,
but I don't know the correct sintax.
For another words:

A B C D
2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the
interval
time between A2 and C2 is < 00:05)
3 09:01 Text2 09:01 Text10 (D3 don't red)
4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the
interval
time is < 00:05 between C5 and A5 both with Text1)
5 09:03 Text4 09:03 Text12
6 09:04 Text6 09:04 ...
7 09:05 Text7 09:05
8 09:06 Text8 09:06
9 09:07 Text9 09:07 Text1 (Don't write red because the
interval
time
is 00:05:00 between C9 and A2 both with Text1)
...

I thought about


=IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF
(C2:C100=A2:A100+"00:05:00";1;0));0)

But don't work.
I woul like any idea.
Thanks in advance.
an

What's sintaxe or formula, please
 

But A4=C4 and D4<B4 as well, so why one red and the other not?

And I still do not know what you want to achieve.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
Sorry for my English and your patience.

D3 isn't red because
A3=C3 but D3<B3

Thanks.
an

"Bob Phillips" wrote:

Sorry, I don't understand. Text11 is not in your example, and you don't
answer whether all cells must be equal in your modified function.

I guess English is not your first language. Is there not an Excel forum
in
your native language?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
3 isn't red because D4<B4 (Text11<Text3)
of where, it does not interest the hour.
It only interests write red if two equal texts will be in an interval
< 5
min

Thanks.
an

"Bob Phillips" wrote:

Why is 4 red but 3 isn't?

Do you want them red if all rows match?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
Hi!

I have:
Time in A2, text in B2, another time in C2 and another text in
D2.
In A and C columns, the times are incremented one minute.
In B column the text is fixed. In C column the text is variable.

In Conditionall Format in D2 cell I use the next formulae:

=IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0)
to write D2 red if condition is true, and work fine, row by row.

Now, I would like change formulae to work with a block, for
example
A2:D100,
but I don't know the correct sintax.
For another words:

A B C D
2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and
the
interval
time between A2 and C2 is < 00:05)
3 09:01 Text2 09:01 Text10 (D3 don't red)
4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but
the
interval
time is < 00:05 between C5 and A5 both with Text1)
5 09:03 Text4 09:03 Text12
6 09:04 Text6 09:04 ...
7 09:05 Text7 09:05
8 09:06 Text8 09:06
9 09:07 Text9 09:07 Text1 (Don't write red because the
interval
time
is 00:05:00 between C9 and A2 both with Text1)
...

I thought about



=IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF
(C2:C100=A2:A100+"00:05:00";1;0));0)

But don't work.
I woul like any idea.
Thanks in advance.
an

What's sintaxe or formula, please
 

Ok.

I would like:
In an interval + or - 00:05, the texts in D column cannot be equal of the
texts in B column (fixed texts) and change to red colour.
For another words:
It cannot have same texts in B (fixed) and D columns.
Only if intervals times 00:05
If the times for same text will be 00:05 or < 00:05, don't have problem.

Yhank you.
an

"Bob Phillips" wrote:

But A4=C4 and D4<B4 as well, so why one red and the other not?

And I still do not know what you want to achieve.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
Sorry for my English and your patience.

D3 isn't red because
A3=C3 but D3<B3

Thanks.
an

"Bob Phillips" wrote:

Sorry, I don't understand. Text11 is not in your example, and you don't
answer whether all cells must be equal in your modified function.

I guess English is not your first language. Is there not an Excel forum
in
your native language?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
3 isn't red because D4<B4 (Text11<Text3)
of where, it does not interest the hour.
It only interests write red if two equal texts will be in an interval
< 5
min

Thanks.
an

"Bob Phillips" wrote:

Why is 4 red but 3 isn't?

Do you want them red if all rows match?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
Hi!

I have:
Time in A2, text in B2, another time in C2 and another text in
D2.
In A and C columns, the times are incremented one minute.
In B column the text is fixed. In C column the text is variable.

In Conditionall Format in D2 cell I use the next formulae:

=IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0)
to write D2 red if condition is true, and work fine, row by row.

Now, I would like change formulae to work with a block, for
example
A2:D100,
but I don't know the correct sintax.
For another words:

A B C D
2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and
the
interval
time between A2 and C2 is < 00:05)
3 09:01 Text2 09:01 Text10 (D3 don't red)
4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but
the
interval
time is < 00:05 between C5 and A5 both with Text1)
5 09:03 Text4 09:03 Text12
6 09:04 Text6 09:04 ...
7 09:05 Text7 09:05
8 09:06 Text8 09:06
9 09:07 Text9 09:07 Text1 (Don't write red because the
interval
time
is 00:05:00 between C9 and A2 both with Text1)
...

I thought about



=IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF
(C2:C100=A2:A100+"00:05:00";1;0));0)

But don't work.
I woul like any idea.
Thanks in advance.
an