 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / March 2008How 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? Hi, Try this =IF(A1>B1,B1+1-A1,B1-A1) Format as hh:mm Mike > 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, nikiteh@gmail.com > Hi, > [quoted text clipped - 11 lines] > > 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 > Hello, > [quoted text clipped - 20 lines] > > > 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 > A1 B1 C1(The formula with cell formatted hh:mm) > 22:15 06:22 08:07 [quoted text clipped - 27 lines] > > > > 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+--(A2>B2)-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)  SignatureRegards Roger Govier > I have set up a timesheet that allows you to clock in and clock out each > day. [quoted text clipped - 4 lines] > 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 > 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? > 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 [quoted text clipped - 27 lines] > > day. > > Is there a formula to manipulate this information? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.