times
 

Hello

I am trying to produce a spreadsheet that will calculate
the hours people have worked and how much they have earned.

There are 4 columns, Start Time, End Time, Breaks and Total

I would like it to be possible to enter the Start Time ,
End Time and Breaks and have the Total calculated for me.

So far I have achieved this, but the resulting total is
still expressed as a time.
For Example,if
Start Time = 09:00
End Time = 17:00
Breaks = 00:30
then the Total = 07:30 indicating 7 hours 30 mins worked.
However I need this to be displayed as 7.5 hours worked,
in order to multiply it by the hourly rate of pay.

Does anyone know a way of doing this?

Thanks in advance

times
 

Angus,

The problem you are experiencing is that time is held as a fraction of 1
day. When you add/subtract time, it automatically formats the result as
time, naturally. You could convert this to a decimal value by multiplying by
24. For instance if A1 holds start time, B1 end time, and C1 breaks then
the decimal time worked is
=(B1-A1-C1)*24

However, I would leave it as time (it looks better) and just multiple the
earnings by 24. So here if D1 holds hours worked and E1 holds pay rate, then
we use
=D1*E1*24

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ANGUS" wrote in message
...
Hello

I am trying to produce a spreadsheet that will calculate
the hours people have worked and how much they have earned.

There are 4 columns, Start Time, End Time, Breaks and Total

I would like it to be possible to enter the Start Time ,
End Time and Breaks and have the Total calculated for me.

So far I have achieved this, but the resulting total is
still expressed as a time.
For Example,if
Start Time = 09:00
End Time = 17:00
Breaks = 00:30
then the Total = 07:30 indicating 7 hours 30 mins worked.
However I need this to be displayed as 7.5 hours worked,
in order to multiply it by the hourly rate of pay.

Does anyone know a way of doing this?

Thanks in advance

times
 

Thank you very much indeed Bob, I would never have guessed
that in a million years!
-----Original Message-----
Angus,

The problem you are experiencing is that time is held as
a fraction of 1
day. When you add/subtract time, it automatically formats
the result as
time, naturally. You could convert this to a decimal
value by multiplying by
24. For instance if A1 holds start time, B1 end time, and
C1 breaks then
the decimal time worked is
=(B1-A1-C1)*24

However, I would leave it as time (it looks better) and
just multiple the
earnings by 24. So here if D1 holds hours worked and E1
holds pay rate, then
we use
=D1*E1*24

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ANGUS" wrote in
message
...
Hello

I am trying to produce a spreadsheet that will calculate
the hours people have worked and how much they have
earned.

There are 4 columns, Start Time, End Time, Breaks and
Total

I would like it to be possible to enter the Start Time ,
End Time and Breaks and have the Total calculated for
me.

So far I have achieved this, but the resulting total is
still expressed as a time.
For Example,if
Start Time = 09:00
End Time = 17:00
Breaks = 00:30
then the Total = 07:30 indicating 7 hours 30 mins
worked.
However I need this to be displayed as 7.5 hours worked,
in order to multiply it by the hourly rate of pay.

Does anyone know a way of doing this?

Thanks in advance




.