I have set up a timesheet that allows you to clock in and clock out each day.
This set up was the preferred method for employees.
However, I now have the problem of being able to add hours worked when
starting shift in the PM of one day and finishing shift in the AM of the next
day.
Is there a formula to manipulate this information?

Hi,

Try this

=IF(A1B1,B1+1-A1,B1-A1)
Format as hh:mm

Mike

"Nikki27" wrote:

I have set up a timesheet that allows you to clock in and clock out each day.
This set up was the preferred method for employees.
However, I now have the problem of being able to add hours worked when
starting shift in the PM of one day and finishing shift in the AM of the next
day.
Is there a formula to manipulate this information?

If Start is in column A, Finish in column B, then try to use this formula
=B2+--(A2B2)-A2
instead of simple =B2-A2

Regards,
Stefi

€žNikki27€� ezt Ã*rta:

I have set up a timesheet that allows you to clock in and clock out each day.
This set up was the preferred method for employees.
However, I now have the problem of being able to add hours worked when
starting shift in the PM of one day and finishing shift in the AM of the next
day.
Is there a formula to manipulate this information?

Hi Nikki

One way
With start time in A1 and End time in B1
=MOD(B1-A1,1)

--
Regards
Roger Govier

"Nikki27" wrote in message
...
I have set up a timesheet that allows you to clock in and clock out each
day.
This set up was the preferred method for employees.
However, I now have the problem of being able to add hours worked when
starting shift in the PM of one day and finishing shift in the AM of the
next
day.
Is there a formula to manipulate this information?

Hello,

Sorry, little confused. Any chance you could send me an example of the
formula in use?

Many Thanks,




"Mike H" wrote:

Hi,

Try this

=IF(A1B1,B1+1-A1,B1-A1)
Format as hh:mm

Mike

"Nikki27" wrote:

I have set up a timesheet that allows you to clock in and clock out each day.
This set up was the preferred method for employees.
However, I now have the problem of being able to add hours worked when
starting shift in the PM of one day and finishing shift in the AM of the next
day.
Is there a formula to manipulate this information?

It depends how the time was entered. Usualy excel will put both the date and
time into the cell. You just have the cell formated to show only the time
(the date is still there). If this is the case then you just have to
subtract the two times and excel will do the rest.


Date and time are store buy excel using the following rules

Jan 1, 1900 is equal to day one. A day is equal to 1. So March 20, 2008 =
39527

If each day is equal to 1 then each hour is equal to 1/24 with midnight
equaling 0. Noon time on Marcxh 20, 2008 is 39527.5 (.5 is one half of a
day).

When you start on March 19 at 8:00 PM and end March 20, 4:00 AM excel does
the following

Start Time
39526 + 20/24 (5/6)= 39526.8333333

End time
39527 + 4/24 (1/6) = 39527.16666666

The differrence = .33333333 which is 8 hours

"Nikki27" wrote:

I have set up a timesheet that allows you to clock in and clock out each day.
This set up was the preferred method for employees.
However, I now have the problem of being able to add hours worked when
starting shift in the PM of one day and finishing shift in the AM of the next
day.
Is there a formula to manipulate this information?

Hi all,

Ok, so I have a few formulas here I can try out. Thank you.

One more question. How would I be able to enter a night shift starting on
say a Monday night and finish on a Tuesday morning. How could I then add
another shift that starts on the Tuesday?
Which cells would I use and how would this work?



"Joel" wrote:

It depends how the time was entered. Usualy excel will put both the date and
time into the cell. You just have the cell formated to show only the time
(the date is still there). If this is the case then you just have to
subtract the two times and excel will do the rest.


Date and time are store buy excel using the following rules

Jan 1, 1900 is equal to day one. A day is equal to 1. So March 20, 2008 =
39527

If each day is equal to 1 then each hour is equal to 1/24 with midnight
equaling 0. Noon time on Marcxh 20, 2008 is 39527.5 (.5 is one half of a
day).

When you start on March 19 at 8:00 PM and end March 20, 4:00 AM excel does
the following

Start Time
39526 + 20/24 (5/6)= 39526.8333333

End time
39527 + 4/24 (1/6) = 39527.16666666

The differrence = .33333333 which is 8 hours

"Nikki27" wrote:

I have set up a timesheet that allows you to clock in and clock out each day.
This set up was the preferred method for employees.
However, I now have the problem of being able to add hours worked when
starting shift in the PM of one day and finishing shift in the AM of the next
day.
Is there a formula to manipulate this information?

A1 B1 C1(The formula with cell formatted hh:mm)
22:15 06:22 08:07

i.e in this example 8hrs 7 minutes

Mike

"Nikki27" wrote:

Hello,

Sorry, little confused. Any chance you could send me an example of the
formula in use?

Many Thanks,




"Mike H" wrote:

Hi,

Try this

=IF(A1B1,B1+1-A1,B1-A1)
Format as hh:mm

Mike

"Nikki27" wrote:

I have set up a timesheet that allows you to clock in and clock out each day.
This set up was the preferred method for employees.
However, I now have the problem of being able to add hours worked when
starting shift in the PM of one day and finishing shift in the AM of the next
day.
Is there a formula to manipulate this information?

Ok, so currently ,in the total hours cells, I already have this formula
=HOUR(D8-C8)
So, how do I use your formula below and in what cell, so that both formulas
apply to the full month ?

Thanks again


"Mike H" wrote:


A1 B1 C1(The formula with cell formatted hh:mm)
22:15 06:22 08:07

i.e in this example 8hrs 7 minutes

Mike

"Nikki27" wrote:

Hello,

Sorry, little confused. Any chance you could send me an example of the
formula in use?

Many Thanks,




"Mike H" wrote:

Hi,

Try this

=IF(A1B1,B1+1-A1,B1-A1)
Format as hh:mm

Mike

"Nikki27" wrote:

I have set up a timesheet that allows you to clock in and clock out each day.
This set up was the preferred method for employees.
However, I now have the problem of being able to add hours worked when
starting shift in the PM of one day and finishing shift in the AM of the next
day.
Is there a formula to manipulate this information?