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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Forum statistics

Threads
1,176,094
Messages
5,901,370
Members
434,887
Latest member
zoath

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