Calculate Time Differences

[Justinian]

I need to pull a report from ADP every day and calculate who took a lunch break less than 30 minutes (see table below). I want to use an IF statement that says if the break time was less than 30 minutes, return a YES. The problem is that when I calculate, some 0:30 results return yes and others return no. I tried formatting the times in "h:mm" but my IF statement still return mixed results. I cannot format as general because some 0:30 results show as 0.020833333333 and others show as 0.023611111111. I also tried using CEILING and FLOOR but the results are not consistent.

I feel like the solution should be easy but it is escaping me.

ADP Verification.xlsx
	E	F	G	H	I	J	K	L	M	N	O
3	8/16/2022	7:55:00 AM	12:00:00 PM	12:30:00 PM	4:25:00 PM					8	Yes
4	8/16/2022	8:32:00 AM	12:30:00 PM	1:00:00 PM	5:02:00 PM					8	Yes
5	8/16/2022	7:55:00 AM	12:00:00 PM	12:30:00 PM	5:00:00 PM					8.58	Yes
6	8/16/2022	7:00:00 AM	11:30:00 AM	12:00:00 PM	2:00:00 PM					6.5	Yes
7	8/16/2022	8:30:00 AM	12:30:00 PM	1:00:00 PM	4:00:00 PM					7	Yes
8	8/16/2022	7:54:00 AM	12:00:00 PM	12:30:00 PM	4:24:00 PM					8	Yes
Sheet1

Cell Formulas
Range	Formula
O3:O8	O3	=IF(H3-G3<"0:30","Yes","No")

 

[gargamalebarbosa]

Could you try like this ?

IF(round(H3-G3,2)<round("0:30",2),"Yes","No")

 

[Justinian]

That is returning a NO for a 0:29 result and a 0:30 result, which is not what I was trying to achieve:

ADP Verification.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P
6	8/16/2022	7:55:00 AM	1:06:00 PM	1:35:00 PM	4:25:00 PM					8.02	No	0:29
7	8/16/2022	7:55:00 AM	12:00:00 PM	12:30:00 PM	4:25:00 PM					8	No	0:30
8	8/16/2022	7:55:00 AM	12:00:00 PM	12:30:00 PM	5:00:00 PM					8.58	No	0:30
Sheet1

Cell Formulas
Range	Formula
O6:O8	O6	=IF(ROUND(H6-G6,2)<ROUND("0:30",2),"Yes","No")
P6:P8	P6	=H6-G6

 

[FormR]

Hi, here's another option you can try:

=IF(MROUND(H3-G3,"00:00:01")<TIME(0,30,0),"Yes","No")

 

[gargamalebarbosa]

That is returning a NO for a 0:29 result and a 0:30 result, which is not what I was trying to achieve:

ADP Verification.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P
6	8/16/2022	7:55:00 AM	1:06:00 PM	1:35:00 PM	4:25:00 PM					8.02	No	0:29
7	8/16/2022	7:55:00 AM	12:00:00 PM	12:30:00 PM	4:25:00 PM					8	No	0:30
8	8/16/2022	7:55:00 AM	12:00:00 PM	12:30:00 PM	5:00:00 PM					8.58	No	0:30
Sheet1

Cell Formulas
Range	Formula
O6:O8	O6	=IF(ROUND(H6-G6,2)<ROUND("0:30",2),"Yes","No")
P6:P8	P6	=H6-G6

Right, it is not working.. sorry

 

[Justinian]

Hi, here's another option you can try:

=IF(MROUND(H3-G3,"00:00:01")<TIME(0,30,0),"Yes","No")

Yes, that works! If I wanted to adjust the formula so that a YES is returned if G3-F3>5:00, how would I do that?

 

[gargamalebarbosa]

 

[Justinian]

View attachment 71850

That does not work, all results are yes.

 

[gargamalebarbosa]

That does not work, all results are yes.

ok. I dont know why ? Because it works for me.
Have a good day .

 

[FormR]

If I wanted to adjust the formula so that a YES is returned if G3-F3>5:00, how would I do that?

Hi, did you try changing this part <TIME(0,30,0) to >TIME(5,0,0)?