Formula to deduct unpaid breaks in time sheet
 

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)

Rick,

Try:

A3:
=A2-A1+(A1A2)

A4:
=IF(A3TIME(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

Replace@mailinator with @tiscali.co.uk

"Rick" wrote in message
...
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)

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

Rick

"Sandy Mann" wrote:

Rick,

Try:

A3:
=A2-A1+(A1A2)

A4:
=IF(A3TIME(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

Replace@mailinator with @tiscali.co.uk

"Rick" wrote in message
...
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)

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

--
Regards

Sandy

Replace@mailinator with @tiscali.co.uk

"Rick" wrote in message
...
Many thanks Sandy, your formula works great. Have a good night.

Rick

"Sandy Mann" wrote:

Rick,

Try:

A3:
=A2-A1+(A1A2)

A4:
=IF(A3TIME(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

Replace@mailinator with @tiscali.co.uk

"Rick" wrote in message
...
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)