Calculate Time Differences

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,496
Office Version
  1. 365
Platform
  1. Windows
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
EFGHIJKLMNO
38/16/20227:55:00 AM12:00:00 PM12:30:00 PM4:25:00 PM8Yes
48/16/20228:32:00 AM12:30:00 PM1:00:00 PM5:02:00 PM8Yes
58/16/20227:55:00 AM12:00:00 PM12:30:00 PM5:00:00 PM8.58Yes
68/16/20227:00:00 AM11:30:00 AM12:00:00 PM2:00:00 PM6.5Yes
78/16/20228:30:00 AM12:30:00 PM1:00:00 PM4:00:00 PM7Yes
88/16/20227:54:00 AM12:00:00 PM12:30:00 PM4:24:00 PM8Yes
Sheet1
Cell Formulas
RangeFormula
O3:O8O3=IF(H3-G3<"0:30","Yes","No")
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

gargamalebarbosa

Board Regular
Joined
Aug 4, 2022
Messages
55
Office Version
  1. 365
Platform
  1. Windows
Could you try like this ?

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

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,496
Office Version
  1. 365
Platform
  1. Windows
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
EFGHIJKLMNOP
68/16/20227:55:00 AM1:06:00 PM1:35:00 PM4:25:00 PM8.02No0:29
78/16/20227:55:00 AM12:00:00 PM12:30:00 PM4:25:00 PM8No0:30
88/16/20227:55:00 AM12:00:00 PM12:30:00 PM5:00:00 PM8.58No0:30
Sheet1
Cell Formulas
RangeFormula
O6:O8O6=IF(ROUND(H6-G6,2)<ROUND("0:30",2),"Yes","No")
P6:P8P6=H6-G6
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,800
Office Version
  1. 365
Platform
  1. Windows
Hi, here's another option you can try:

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

gargamalebarbosa

Board Regular
Joined
Aug 4, 2022
Messages
55
Office Version
  1. 365
Platform
  1. Windows
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
EFGHIJKLMNOP
68/16/20227:55:00 AM1:06:00 PM1:35:00 PM4:25:00 PM8.02No0:29
78/16/20227:55:00 AM12:00:00 PM12:30:00 PM4:25:00 PM8No0:30
88/16/20227:55:00 AM12:00:00 PM12:30:00 PM5:00:00 PM8.58No0:30
Sheet1
Cell Formulas
RangeFormula
O6:O8O6=IF(ROUND(H6-G6,2)<ROUND("0:30",2),"Yes","No")
P6:P8P6=H6-G6
Right, it is not working.. sorry
 

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,496
Office Version
  1. 365
Platform
  1. Windows
Hi, here's another option you can try:

Excel Formula:
=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

Board Regular
Joined
Aug 4, 2022
Messages
55
Office Version
  1. 365
Platform
  1. Windows
1660760929398.png
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,800
Office Version
  1. 365
Platform
  1. Windows
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)?
 

Forum statistics

Threads
1,175,502
Messages
5,897,800
Members
434,677
Latest member
Aurelied

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top