Deduct 15 minutes if cell equals

dan17602

New Member
Joined
Mar 20, 2011
Messages
9
Hi All,

I am currently working on an adherence project & am a little stuck on a formula for the following

Column B holds codes ie, Break
Column E holds in duration in that code

The formula needs to check column B to check its Break, then if duration is over 00:15:00 to count anything over this allocation. If the duration is under 00:15:00 to appear blank?

Is there a formula that would work in both Excel 2003 & 2007 for this?

Any help is much appreciated
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

delaneyjm

Well-known Member
Joined
Apr 22, 2009
Messages
624
Perhaps something like this?

Excel Workbook
BCDEFG
1StatusDurationCheck
2Break0:11:00 
3Break0:20:000:05:00
4Break0:22:000:07:00
5Break0:18:000:03:00
6Break0:22:000:07:00
70:15:00
80:16:00
9Break0:18:000:03:00
100:19:00
11Break0:23:000:08:00
120:10:00
130:20:00
14Break0:12:00
150:25:00
16Break0:21:000:06:00
170:24:00
180:15:00
190:20:00
20Break0:16:000:01:00
Sheet1
 

dan17602

New Member
Joined
Mar 20, 2011
Messages
9
Hi delaneyjm,

Thank you for your reply, your example matches exactly what I was after, however when I inputted those formulas (with sheet 1 in the same format) under column G I was given a "#REF!" error, do you know why this could be?

Thanks,
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,929
Office Version
  1. 365
Platform
  1. Windows
It looks like the formula didn't post correctly, maybe

=IF(B2="Break",IF(E2 > TIME<TIME(0,15,0),"",E2-TIME(0,15,0)),"")

or

=IF(AND(B2="Break",E2 > --"0:15"),E2-"0:15","")
 

dan17602

New Member
Joined
Mar 20, 2011
Messages
9
Hi Jason,

The 2nd formula you put "=IF(AND(B2="Break",E2 > --"0:15"),E2-"0:15","")" worked spot on, exacly what I was after.

Thanks again to both of you for your help

Dan
 

kobusod

New Member
Joined
Mar 27, 2011
Messages
3
Hey guys,can someone please tell me how i can post a query?I am new,and it is urgent
 

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,502
Members
414,073
Latest member
Contilly

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