Time Function in Conditional Formatting

[Michele317]

Hello,
I'm having some trouble with the conditional formatting.
I would like to insert a formula in it that includes the "Time" function.
What I write is:

=O$3>N$3+TIME(0,30,0)

The conditional formatting have simply to check if the time value in a cell is greater than the time value in another cell + 30 minutes.
I tried to delete the "TIME" function part and it seems to work. So, the other 2 cells are not the problem.

How can I fix this?

Thank you,
Michele

 

[Michele317]

By keeping working on the sheet, I found out another problem, also with the conditional formatting.
The ROUND function seems not to work.
At this point, I think I'm doing something wrong in how I enter the function, but I really don't know what.
I write down what I have wrote:

=ROUND($M6,5)>=ROUND($L6,5)

or in my native language, that is the language i'm using in excel:

=ARROTONDA($M6;5)>=ARROTONDA($L6;5)

 

[Joe4]

Are you sure that all the values you are checking are entered as valid dates/numbers and not as text?

What is your value in cell M6?
If you enter this formula in any blank cell, what does it return?

=ROUND($M6,5)

(or in your native languages):

=ARROTONDA($M6;5)

Is that doesn't seem to help explain the issue, please show us some of your data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Michele317]

Hello @Joe4 and thank you for your answer.

What is your value in cell M6?
If you enter this formula in any blank cell, what does it return?

It's a simple value that I write in the cell. It's not a formula result. If I do the ROUND() on it, it simply gives me back the value.
Different case is for L6. In this case the value is the result of a formula.
Both cells are formatted using a personalized format, which is hh:mm.

If I try to do the same formulas in a normal cell, I get what I want:
- TRUE/FALSE in the first case
- The result, as already said above.

I post below the mini sheet.

Flex_Straordinari.xlsm
	B	C	D	E			H	I	J	K	L	M	N	O	P	Q	R	S
2	Giorno		Entrata	Uscita			Entrata	Uscita	Durata L	Delta	Straordinario	--> preso	Permesso	--> usato	Flex	Durata N	Ticket	Check
3		ven
4	02/01/1904	sab
5	03/01/1904	dom
6	04/01/1904	lun	11:57	13:00			13:52	18:19	05:30	-02:30			02:00	02:00	-00:30	08:00	Si	✗
7	05/01/1904	mar	08:42	13:00			13:52	18:19	08:45	00:45	00:45	00:45			00:00	08:00	Si	✗
8	06/01/1904	mer	08:23	13:04			13:45	18:30	09:26	01:26	01:26	01:26			00:00	08:00	Si	✓
9	07/01/1904	gio	08:38	13:10			13:46	18:14	09:00	01:00	01:00	01:00			00:00	08:00	Si	✗
10	08/01/1904	ven	08:32	13:06			13:44	16:35	07:25	02:25	02:25	02:25			00:00	05:00	Si	✓
Template_28

Cell Formulas
Range	Formula
C3:C10	C3	=B3
J3:J10	J3	=IF(OR(D3="Festivo",AND(D3="Ferie",H3=""),D3=""),"",IF(AND(D3="Ferie",E3=""),I3-H3,IF(AND(OR(H3="Ferie",H3="Festivo"),I3=""),E3-D3,(E3-D3)+(G3-F3)+(I3-H3))))
K3:K10	K3	=IF(J3="","",IF(OR(D3="Ferie",H3="Ferie",H3="Festivo"),IF(J3-Settings!$C$6/2<0,ROUND(J3-Settings!$C$6/2,10),J3-Settings!$C$6/2),IF(WEEKDAY(B3)=6,J3-Settings!$C$7,J3-Settings!$C$6)))
L3:L10	L3	=IF(OR(K3="",D3=""),"",IF(AND(WEEKDAY(B3)=6,K3>TIME(0,30,0)),IF(E3-D3<=TIME(5,0,0),K3,((D3+TIME(5,0,0)-D3)+(I3-H3)-TIME(5,0,0))),IF(K3>TIME(0,30,0),K3,"")))
P3:P10	P3	=IF(K3="","",IF(K3<0,IF(K3<-TIME(0,30,0),IF(O3="","←inserire",IF(O3>=ROUND(N3,5),IF(O3<=N3+TIME(0,30,0),ROUND(K3+O3,5),"←grande"),"←piccolo")),K3),IF(K3<TIME(0,30,0),K3,IF(ROUND(M3,5)>ROUND(L3,5),"←grande",IF(K3-M3>TIME(0,30,0),TIME(0,30,0),IF(K3-M3<0,ROUND(K3-M3,0),K3-M3))))))
Q3:Q10	Q3	=IF(OR(P3="←inserire",P3="←grande",P3="←piccolo"),"←",IF(J3="","",J3-M3-P3+O3))
R3:R10	R3	=IF(J3="","",IF(AND(OR(WEEKDAY(B3)=2,WEEKDAY(B3)=3,WEEKDAY(B3)=4,WEEKDAY(B3)=5),AND(D3<>"Festivo",D3<>"Ferie")),"Si",IF(AND(WEEKDAY(B3)=6,J3>Settings!$C$7+TIME(1,0,0),AND(D3<>"Festivo",D3<>"Ferie")),"Si","No")))
S3:S10	S3	=IFERROR(IF(OR(D3="",AND(D3="Festivo",H3=""),AND(H3="Festivo",D3=""),AND(D3="Ferie",H3=""),AND(H3="Ferie",D3="")),"",IF(OR(AND(IF(AND(M3=L3,M3<>""),Q3=J3-M3,Q3=J3-P3-M3),M3<=L3),AND(Q3=J3+O3,ROUND(Q3,2)=ROUND(IF(WEEKDAY(B3)=6,Settings!$C$7,Settings!$C$6)+P3,2))),"✓","✗")),"✗")
B4:B10	B4	=C3+1
N3:N10	N3	=IF(K3<-TIME(0,30,0),ABS(K3)-TIME(0,30,0),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H6:H10	Expression	=$H6="Festivo"	text	NO
H6:H10	Expression	=$H6="Ferie"	text	NO
D6:D10	Expression	=$D6="Ferie"	text	NO
D6:D10	Expression	=$D6="Festivo"	text	NO
M6:M10	Expression	=ARROTONDA($M6;5)>=ARROTONDA($L6;5)	text	NO
O6:O10	Expression	=P$6="←inserire"	text	NO
P3:P30	Cell Value	="←inserire"	text	NO
Q6:Q10	Expression	=$P6="←inserire"	text	NO
Q6:Q10	Expression	=$P6="←grande"	text	NO
Q6:Q10	Expression	=$P6="←piccolo"	text	NO
O6:O10	Expression	=O$3>N$3+TIME(0;30;0)	text	NO
P3:P30	Cell Value	="←grande"	text	NO
P3:P30	Cell Value	="←piccolo"	text	NO
O6:O10	Expression	=P$6="←piccolo"	text	NO
S3:S30	Cell Value	="✗"	text	NO
S3:S30	Cell Value	="✓"	text	NO
B3:C30,H3:H5,H11:H30	Expression	=$H3="Festivo"	text	NO
B3:C30,H3:H5,H11:H30	Expression	=$H3="Ferie"	text	NO
B3:D5,B11:D30,B6:C10	Expression	=$D3="Festivo"	text	NO
B3:D5,B11:D30,B6:C10	Expression	=$D3="Ferie"	text	NO

 

[Joe4]

So based on your on the example that you posted, can you point out a cell that is not working as you would expect?
M7 and L7 look the same to me, so they seem to meet your Conditional Formatting formula, and M7 is highlighted pink.

 

[Michele317]

The column O and the column M (since I'm going to repeat the conditional formatting for the whole columns).
You can see the cell O6 and M7 for, repsecting, the TIME() problem and the ROUND() problem.

Also, to see if the conditional formatting works you can:
- TIME() problem: try to set the cell O6 to "02:31" (it should color red)
-ROUND() problem: try to set the cell M7 to "00:45" (it should NOT color red)

Thank you

 

[Joe4]

Note that in your formula for column L, you are sometimes returning a text value:

=IF(OR(K3="",D3=""),"",...

The part in red will interpretted as Text, so you are comparing text to date/time.
So you may not get the results that you want or expect when comparing different data types.
You probably do NOT want to return text values in your date/time columns.

 

[Michele317]

Note that in your formula for column L, you are sometimes returning a text value:

=IF(OR(K3="",D3=""),"",...

The part in red will interpretted as Text, so you are comparing text to date/time.
So you may not get the results that you want or expect when comparing different data types.
You probably do NOT want to return text values in your date/time columns.

Thank you for noticing that. But it return a text value only when I do not have to do any calculation (weekend), so the whole row is going to be blank.
In fact, K3 is blank when it's a saturday. And if K3 is blank, the whole row doesn't compute anything.

Also, in this case i'm returning a value, which is 00:45 and I think the output is not a text value since, if I change the format to General, it gives me a decimal number.
Also, I tried to do the ROUND() function on it in a normal cell (not in Conditional Formattin) and it works just fine.

 

[Joe4]

- TIME() problem: try to set the cell O6 to "02:31" (it should color red)

Note that you have 3 different Conditional Formatting rules running on cells O6:O10.
This could create problems, because they may be conflicting with each other, especially if you are not using "Stop if True".
If you do not use that setting, it will run all the rules, and the last one will be the one that gets applied.

-ROUND() problem: try to set the cell M7 to "00:45" (it should NOT color red)

Why should it NOT color red?
It meets the Condition you set:

=ROUND($M6,5)>=ROUND($L6,5)

Note that you formula says "greater than OR equal to", and the values appear to be equal!

 

[Michele317]

Note that you have 3 different Conditional Formatting rules running on cells O6:O10.
This could create problems, because they may be conflicting with each other, especially if you are not using "Stop if True".
If you do not use that setting, it will run all the rules, and the last one will be the one that gets applied.


Why should it NOT color red?
It meets the Condition you set:

=ROUND($M6,5)>=ROUND($L6,5)

Note that you formula says "greater than OR equal to", and the values appear to be equal!

I feel so dumb right now ahahah Yeah, you are right. That solves the ROUND() problem.
Thank you so much.

Still trying with the TIME() one but without success