How do I include Nightshifts on a daily timesheet
 

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?

How do I include Nightshifts on a daily timesheet
 

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?

How do I include Nightshifts on a daily timesheet
 

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?

How do I include Nightshifts on a daily timesheet
 

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?

How do I include Nightshifts on a daily timesheet
 

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?

How do I include Nightshifts on a daily timesheet
 

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?

How do I include Nightshifts on a daily timesheet
 

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?

How do I include Nightshifts on a daily timesheet
 

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?

How do I include Nightshifts on a daily timesheet
 

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?