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
 

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
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
14,761
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
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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