Formula to deduct unpaid breaks in time sheet

[Guest]

Hi, I have created a Time sheet to work out hours worked but need to create a
column to show unpaid breaks, if they have worked more than 5 hours, in the
following format 00:30 ( = 30 minutes unpaid break) and then a Formula to
show total paid hours as per below. Can anyone help please;
A1 = 14:00 (Start Time)
A2 = 22:00 (End Time)
A3 = 08:00 (Total Hours worked)
A4 = 00:30 (unpaid break) FORMULA FOR A4 REQUIRED
A5 = 07:30 (Paid hours)
A6 = £8.00 (Hourly rate)
A7 = Total Pay (Paid Hours)

 

[Sandy Mann]

Rick,

Try:

A3:
=A2-A1+(A1>A2)

A4:
=IF(A3>TIME(5,30,0),TIME(0,30,0),TIME(0,0,0))

A5:
=A3-A4

A7:
=A5*24*A6

Format A1:A5 as time and A7 as Currency

I assumed that if you worked exactly 5 hours then you are not intitled to a
break

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Guest]

Many thanks Sandy, your formula works great. Have a good night.

Rick

 

[Sandy Mann]

You're welcome, and yes I am just goint to bed - well perhaps just ONE more
download <g>

--
Regards

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk