Deduct 15 minutes if cell equals

dan17602

New Member
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

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

delaneyjm

Well-known Member
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
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
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
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
Hey guys,can someone please tell me how i can post a query?I am new,and it is urgent

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,323
Messages
5,836,637
Members
430,442
Latest member
Zephreo

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.

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

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