Spread Sheet Employee Hours Issue
 

Here's the Excel time sheet I've been working on, and here's the problem:

This is an example of an employee who works the overnight shift:
When we enter 21:00 as an 'In' time, and 09:00 as the 'Out' time, the total
hrs do not calculate properly.

If I convert 09:00 to 33:00 (24:00 + 09:00) it works properly, but this is
not an acceptable solution since I want to turn this worksheet over to the
managers to enter employee's times, and they'll not 'get' it.

Just FYI, this is for an assisted living home that has 24 hr staffing. They
have been using military time, and it confuses everyone when it comes to
totaling the hours, so we want Excel to do the calculating.

Do you think you can fix this mess for us?? You'll be my hero!

Spread Sheet Employee Hours Issue
 

Let's say A1 =21:00, B1 =09:00
C1 =(B1-A1)+(A1B1)


"Kelsey" wrote:

Here's the Excel time sheet I've been working on, and here's the problem:

This is an example of an employee who works the overnight shift:
When we enter 21:00 as an 'In' time, and 09:00 as the 'Out' time, the total
hrs do not calculate properly.

If I convert 09:00 to 33:00 (24:00 + 09:00) it works properly, but this is
not an acceptable solution since I want to turn this worksheet over to the
managers to enter employee's times, and they'll not 'get' it.

Just FYI, this is for an assisted living home that has 24 hr staffing. They
have been using military time, and it confuses everyone when it comes to
totaling the hours, so we want Excel to do the calculating.

Do you think you can fix this mess for us?? You'll be my hero!

Spread Sheet Employee Hours Issue
 

=IF(E11E10, E11-E10,(E11+24)-E10)

Where E11 is the finish time and E10 is the start time.



"Kelsey" wrote:

Here's the Excel time sheet I've been working on, and here's the problem:

This is an example of an employee who works the overnight shift:
When we enter 21:00 as an 'In' time, and 09:00 as the 'Out' time, the total
hrs do not calculate properly.

If I convert 09:00 to 33:00 (24:00 + 09:00) it works properly, but this is
not an acceptable solution since I want to turn this worksheet over to the
managers to enter employee's times, and they'll not 'get' it.

Just FYI, this is for an assisted living home that has 24 hr staffing. They
have been using military time, and it confuses everyone when it comes to
totaling the hours, so we want Excel to do the calculating.

Do you think you can fix this mess for us?? You'll be my hero!

Spread Sheet Employee Hours Issue
 

I click ENTER key too soon from my previous reply...
make sure format cell as hh:mm


"Kelsey" wrote:

Here's the Excel time sheet I've been working on, and here's the problem:

This is an example of an employee who works the overnight shift:
When we enter 21:00 as an 'In' time, and 09:00 as the 'Out' time, the total
hrs do not calculate properly.

If I convert 09:00 to 33:00 (24:00 + 09:00) it works properly, but this is
not an acceptable solution since I want to turn this worksheet over to the
managers to enter employee's times, and they'll not 'get' it.

Just FYI, this is for an assisted living home that has 24 hr staffing. They
have been using military time, and it confuses everyone when it comes to
totaling the hours, so we want Excel to do the calculating.

Do you think you can fix this mess for us?? You'll be my hero!

Spread Sheet Employee Hours Issue
 

I wouldn't recommend adding 24 days, Cin. (It will give an answer of 564
hours instead of 12).
1 day would make more sense.
--
David Biddulph

"Cin" wrote in message
...
=IF(E11E10, E11-E10,(E11+24)-E10)

Where E11 is the finish time and E10 is the start time.

"Kelsey" wrote:

Here's the Excel time sheet I've been working on, and here's the problem:

This is an example of an employee who works the overnight shift:
When we enter 21:00 as an 'In' time, and 09:00 as the 'Out' time, the
total
hrs do not calculate properly.

If I convert 09:00 to 33:00 (24:00 + 09:00) it works properly, but this
is
not an acceptable solution since I want to turn this worksheet over to
the
managers to enter employee's times, and they'll not 'get' it.

Just FYI, this is for an assisted living home that has 24 hr staffing.
They
have been using military time, and it confuses everyone when it comes to
totaling the hours, so we want Excel to do the calculating.

Do you think you can fix this mess for us?? You'll be my hero!

Spread Sheet Employee Hours Issue
 

If you have it formated as 24h clock it all works with a +24, sorry - forgot
to mention that bit! ;-)

"David Biddulph" wrote:

I wouldn't recommend adding 24 days, Cin. (It will give an answer of 564
hours instead of 12).
1 day would make more sense.
--
David Biddulph

"Cin" wrote in message
...
=IF(E11E10, E11-E10,(E11+24)-E10)

Where E11 is the finish time and E10 is the start time.

"Kelsey" wrote:

Here's the Excel time sheet I've been working on, and here's the problem:

This is an example of an employee who works the overnight shift:
When we enter 21:00 as an 'In' time, and 09:00 as the 'Out' time, the
total
hrs do not calculate properly.

If I convert 09:00 to 33:00 (24:00 + 09:00) it works properly, but this
is
not an acceptable solution since I want to turn this worksheet over to
the
managers to enter employee's times, and they'll not 'get' it.

Just FYI, this is for an assisted living home that has 24 hr staffing.
They
have been using military time, and it confuses everyone when it comes to
totaling the hours, so we want Excel to do the calculating.

Do you think you can fix this mess for us?? You'll be my hero!

Spread Sheet Employee Hours Issue
 

What David was saying is valid.

"Cin" wrote in message
...
=IF(E11E10, E11-E10,(E11+24)-E10)

Where E11 is the finish time and E10 is the start time.

With 01:00 in E11 and 23:00 in E10

=IF(E11E10, E11-E10,(E11+24)-E10)

returns 23.0833333, formtted as hh:mm it does indeed only *show* as 2:00 but
it nevertheless still has an extra 23 days included in the value actually
held in the
cell.

If you want to multiply the elepsed time by a pay rate the normal way of
doing so is to multiply the *time* value by 24 to convert it to decimal
hours and then multiply the decimal hours by the pay rate. ie if the result
of your formula is in cell F12 and the pay rate is 10 then =F12*24*10
formatted as currency returns
5,540.00 dollars or pounds for a two hour shift - one hell of a pay for a
two hour shift!

Hours that cross midnight can be calculated by:

=E11-E10+(E10E11)
or
=MOD(E11-E12,1)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Cin" wrote in message
...
If you have it formated as 24h clock it all works with a +24, sorry -
forgot
to mention that bit! ;-)

"David Biddulph" wrote:

I wouldn't recommend adding 24 days, Cin. (It will give an answer of 564
hours instead of 12).
1 day would make more sense.
--
David Biddulph

"Cin" wrote in message
...
=IF(E11E10, E11-E10,(E11+24)-E10)

Where E11 is the finish time and E10 is the start time.

"Kelsey" wrote:

Here's the Excel time sheet I've been working on, and here's the
problem:

This is an example of an employee who works the overnight shift:
When we enter 21:00 as an 'In' time, and 09:00 as the 'Out' time, the
total
hrs do not calculate properly.

If I convert 09:00 to 33:00 (24:00 + 09:00) it works properly, but
this
is
not an acceptable solution since I want to turn this worksheet over to
the
managers to enter employee's times, and they'll not 'get' it.

Just FYI, this is for an assisted living home that has 24 hr staffing.
They
have been using military time, and it confuses everyone when it comes
to
totaling the hours, so we want Excel to do the calculating.

Do you think you can fix this mess for us?? You'll be my hero!

Spread Sheet Employee Hours Issue
 

Hours that cross midnight can be calculated by:

=E11-E10+(E10E11)
or
=MOD(E11-E12,1)

What I should have written of course was:

Any hours including hours that cross midnight.......
--
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Sandy Mann" wrote in message
...
What David was saying is valid.

"Cin" wrote in message
...
=IF(E11E10, E11-E10,(E11+24)-E10)

Where E11 is the finish time and E10 is the start time.

With 01:00 in E11 and 23:00 in E10

=IF(E11E10, E11-E10,(E11+24)-E10)

returns 23.0833333, formtted as hh:mm it does indeed only *show* as 2:00
but
it nevertheless still has an extra 23 days included in the value actually
held in the
cell.

If you want to multiply the elepsed time by a pay rate the normal way of
doing so is to multiply the *time* value by 24 to convert it to decimal
hours and then multiply the decimal hours by the pay rate. ie if the
result
of your formula is in cell F12 and the pay rate is 10 then =F12*24*10
formatted as currency returns
5,540.00 dollars or pounds for a two hour shift - one hell of a pay for a
two hour shift!

Hours that cross midnight can be calculated by:

=E11-E10+(E10E11)
or
=MOD(E11-E12,1)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Cin" wrote in message
...
If you have it formated as 24h clock it all works with a +24, sorry -
forgot
to mention that bit! ;-)

"David Biddulph" wrote:

I wouldn't recommend adding 24 days, Cin. (It will give an answer of
564
hours instead of 12).
1 day would make more sense.
--
David Biddulph

"Cin" wrote in message
...
=IF(E11E10, E11-E10,(E11+24)-E10)

Where E11 is the finish time and E10 is the start time.

"Kelsey" wrote:

Here's the Excel time sheet I've been working on, and here's the
problem:

This is an example of an employee who works the overnight shift:
When we enter 21:00 as an 'In' time, and 09:00 as the 'Out' time,
the
total
hrs do not calculate properly.

If I convert 09:00 to 33:00 (24:00 + 09:00) it works properly, but
this
is
not an acceptable solution since I want to turn this worksheet over
to
the
managers to enter employee's times, and they'll not 'get' it.

Just FYI, this is for an assisted living home that has 24 hr
staffing.
They
have been using military time, and it confuses everyone when it comes
to
totaling the hours, so we want Excel to do the calculating.

Do you think you can fix this mess for us?? You'll be my hero!