Deduct 15 minutes if cell equals

[dan17602]

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

 

[delaneyjm]

Perhaps something like this?

Excel Workbook
	B	C	D	E	F	G
1	Status			Duration		Check
2	Break			0:11:00
3	Break			0:20:00		0:05:00
4	Break			0:22:00		0:07:00
5	Break			0:18:00		0:03:00
6	Break			0:22:00		0:07:00
7				0:15:00
8				0:16:00
9	Break			0:18:00		0:03:00
10				0:19:00
11	Break			0:23:00		0:08:00
12				0:10:00
13				0:20:00
14	Break			0:12:00
15				0:25:00
16	Break			0:21:00		0:06:00
17				0:24:00
18				0:15:00
19				0:20:00
20	Break			0:16:00		0:01:00
Sheet1

 

[dan17602]

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]

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]

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]

Hey guys,can someone please tell me how i can post a query?I am new,and it is urgent