If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I set up a time sheet in Excel?
What I need to do is have people enter the time they start, what time they
leave for lunch, come back from lunch and leave at the end of the day. I want Excel to automatically calculate the number of hours and minutes for each of these so that I can then add up the total number of hours and minutes worked each day. I am having a hard time formatting the cells (do I format them as time or numbers?) and then whenever I try to add or subtract hours, how do you deal with the 12:00 hour, which then seems to mess up any formulas. For example, if someone clocks in at 7 and goes to lunch at 1:30 - how can you create a formula where Excel will get the correct answer of 6:30 minutes worked? HELP! |
#2
|
|||
|
|||
How do I set up a time sheet in Excel?
Hi Julie,
Let A is column for Start Time , B for leave for lunch,C for come back from lunch and D for leave at the end then add this formula in column E say in cell E2. =if(b2a2,a2-b2+12+d2-c2,b2-a2+d2-c2) Harshawardhan shastri ================================================== =========== "Julie Holmberg" wrote: What I need to do is have people enter the time they start, what time they leave for lunch, come back from lunch and leave at the end of the day. I want Excel to automatically calculate the number of hours and minutes for each of these so that I can then add up the total number of hours and minutes worked each day. I am having a hard time formatting the cells (do I format them as time or numbers?) and then whenever I try to add or subtract hours, how do you deal with the 12:00 hour, which then seems to mess up any formulas. For example, if someone clocks in at 7 and goes to lunch at 1:30 - how can you create a formula where Excel will get the correct answer of 6:30 minutes worked? HELP! |
#4
|
|||
|
|||
How do I set up a time sheet in Excel?
Or
=D2-A2-(C2-B2) -- Regards, Peo Sjoblom "HARSHAWARDHAN. S .SHASTRI" wrote in message ... Hi Julie, Let A is column for Start Time , B for leave for lunch,C for come back from lunch and D for leave at the end then add this formula in column E say in cell E2. =if(b2a2,a2-b2+12+d2-c2,b2-a2+d2-c2) Harshawardhan shastri ================================================== =========== "Julie Holmberg" wrote: What I need to do is have people enter the time they start, what time they leave for lunch, come back from lunch and leave at the end of the day. I want Excel to automatically calculate the number of hours and minutes for each of these so that I can then add up the total number of hours and minutes worked each day. I am having a hard time formatting the cells (do I format them as time or numbers?) and then whenever I try to add or subtract hours, how do you deal with the 12:00 hour, which then seems to mess up any formulas. For example, if someone clocks in at 7 and goes to lunch at 1:30 - how can you create a formula where Excel will get the correct answer of 6:30 minutes worked? HELP! |
#5
|
|||
|
|||
How do I set up a time sheet in Excel?
Just subtract and format as hh:mm
-- Regards, Peo Sjoblom "Julie Holmberg" Julie wrote in message ... What I need to do is have people enter the time they start, what time they leave for lunch, come back from lunch and leave at the end of the day. I want Excel to automatically calculate the number of hours and minutes for each of these so that I can then add up the total number of hours and minutes worked each day. I am having a hard time formatting the cells (do I format them as time or numbers?) and then whenever I try to add or subtract hours, how do you deal with the 12:00 hour, which then seems to mess up any formulas. For example, if someone clocks in at 7 and goes to lunch at 1:30 - how can you create a formula where Excel will get the correct answer of 6:30 minutes worked? HELP! |
#6
|
|||
|
|||
How do I set up a time sheet in Excel?
I guess you intended =if(b2a2,b2-a2+12+d2-c2,b2-a2+d2-c2) rather than
=if(b2a2,a2-b2+12+d2-c2,b2-a2+d2-c2)? Adding 12 is fine if the inputs are all numbers of hours (rather than times), so if it were 1.5 rather than 1:30 your formula (as modified) would work in those conditions, but I wouldn't recommend going down that route. [If you did go that way, you may want to allow for coming back from lunch at 12:45?] -- David Biddulph "HARSHAWARDHAN. S .SHASTRI" wrote in message ... Hi Julie, Let A is column for Start Time , B for leave for lunch,C for come back from lunch and D for leave at the end then add this formula in column E say in cell E2. =if(b2a2,a2-b2+12+d2-c2,b2-a2+d2-c2) Harshawardhan shastri ================================================== =========== "Julie Holmberg" wrote: What I need to do is have people enter the time they start, what time they leave for lunch, come back from lunch and leave at the end of the day. I want Excel to automatically calculate the number of hours and minutes for each of these so that I can then add up the total number of hours and minutes worked each day. I am having a hard time formatting the cells (do I format them as time or numbers?) and then whenever I try to add or subtract hours, how do you deal with the 12:00 hour, which then seems to mess up any formulas. For example, if someone clocks in at 7 and goes to lunch at 1:30 - how can you create a formula where Excel will get the correct answer of 6:30 minutes worked? HELP! |
#7
|
|||
|
|||
How do I set up a time sheet in Excel?
Thank you for the help - I tried this but I think it needs more in the
formula. The problem being if they go to lunch say at 11:45 and come back at 12:45, then the last part of the equation needs to take into consideration the same 12 hour issue, right? "HARSHAWARDHAN. S .SHASTRI" wrote: Hi Julie, Let A is column for Start Time , B for leave for lunch,C for come back from lunch and D for leave at the end then add this formula in column E say in cell E2. =if(b2a2,a2-b2+12+d2-c2,b2-a2+d2-c2) Harshawardhan shastri ================================================== =========== "Julie Holmberg" wrote: What I need to do is have people enter the time they start, what time they leave for lunch, come back from lunch and leave at the end of the day. I want Excel to automatically calculate the number of hours and minutes for each of these so that I can then add up the total number of hours and minutes worked each day. I am having a hard time formatting the cells (do I format them as time or numbers?) and then whenever I try to add or subtract hours, how do you deal with the 12:00 hour, which then seems to mess up any formulas. For example, if someone clocks in at 7 and goes to lunch at 1:30 - how can you create a formula where Excel will get the correct answer of 6:30 minutes worked? HELP! |
#8
|
|||
|
|||
How do I set up a time sheet in Excel?
No it doesn't, as long as they stay within the same date.
If they take lunch at 11:45 PM and come back at 12:45 AM it needs to be considered but not otherwise. one Excel hour = 1/24th of a day and one day = 1 so basically if you go at lunch at 11:45 AM you just subtract =0.53125-0.489583333333333 is 0.0416666666666667 formatted as time hh:mm equals 1:00 -- Regards, Peo Sjoblom "Julie Holmberg" wrote in message ... Thank you for the help - I tried this but I think it needs more in the formula. The problem being if they go to lunch say at 11:45 and come back at 12:45, then the last part of the equation needs to take into consideration the same 12 hour issue, right? "HARSHAWARDHAN. S .SHASTRI" wrote: Hi Julie, Let A is column for Start Time , B for leave for lunch,C for come back from lunch and D for leave at the end then add this formula in column E say in cell E2. =if(b2a2,a2-b2+12+d2-c2,b2-a2+d2-c2) Harshawardhan shastri ================================================== =========== "Julie Holmberg" wrote: What I need to do is have people enter the time they start, what time they leave for lunch, come back from lunch and leave at the end of the day. I want Excel to automatically calculate the number of hours and minutes for each of these so that I can then add up the total number of hours and minutes worked each day. I am having a hard time formatting the cells (do I format them as time or numbers?) and then whenever I try to add or subtract hours, how do you deal with the 12:00 hour, which then seems to mess up any formulas. For example, if someone clocks in at 7 and goes to lunch at 1:30 - how can you create a formula where Excel will get the correct answer of 6:30 minutes worked? HELP! |
#9
|
|||
|
|||
How do I set up a time sheet in Excel?
Yes, that's right. Perhaps my message to in reply to Harshawardhan's post
hasn't got to your news server yet? You could use =MOD(B2-A2,0.5)+MOD(D2-A2,0.5), but I would stick to my recommendation of using either the 24 hour clock or specifically saying 1:30 PM, not just 1:30. -- David Biddulph "Julie Holmberg" wrote in message ... Thank you for the help - I tried this but I think it needs more in the formula. The problem being if they go to lunch say at 11:45 and come back at 12:45, then the last part of the equation needs to take into consideration the same 12 hour issue, right? "HARSHAWARDHAN. S .SHASTRI" wrote: Hi Julie, Let A is column for Start Time , B for leave for lunch,C for come back from lunch and D for leave at the end then add this formula in column E say in cell E2. =if(b2a2,a2-b2+12+d2-c2,b2-a2+d2-c2) Harshawardhan shastri ================================================== =========== "Julie Holmberg" wrote: What I need to do is have people enter the time they start, what time they leave for lunch, come back from lunch and leave at the end of the day. I want Excel to automatically calculate the number of hours and minutes for each of these so that I can then add up the total number of hours and minutes worked each day. I am having a hard time formatting the cells (do I format them as time or numbers?) and then whenever I try to add or subtract hours, how do you deal with the 12:00 hour, which then seems to mess up any formulas. For example, if someone clocks in at 7 and goes to lunch at 1:30 - how can you create a formula where Excel will get the correct answer of 6:30 minutes worked? HELP! |
#10
|
|||
|
|||
How do I set up a time sheet in Excel?
I did get your posting, thank you. I have tried the formula and it is not
working. I amgoing to try and post it in this message so you can see what I'm trying. Thank you again... Mon 9/8/2008 7:00 (cell d13) 11:45 (cell d14) 12:45 (cell d15) 4:00 (cell d16) Here is the formula I am trying: =IF(D14D13,D14-D13+12+D16-D15,D14-D13+D16-D15) "David Biddulph" wrote: Yes, that's right. Perhaps my message to in reply to Harshawardhan's post hasn't got to your news server yet? You could use =MOD(B2-A2,0.5)+MOD(D2-A2,0.5), but I would stick to my recommendation of using either the 24 hour clock or specifically saying 1:30 PM, not just 1:30. -- David Biddulph "Julie Holmberg" wrote in message ... Thank you for the help - I tried this but I think it needs more in the formula. The problem being if they go to lunch say at 11:45 and come back at 12:45, then the last part of the equation needs to take into consideration the same 12 hour issue, right? "HARSHAWARDHAN. S .SHASTRI" wrote: Hi Julie, Let A is column for Start Time , B for leave for lunch,C for come back from lunch and D for leave at the end then add this formula in column E say in cell E2. =if(b2a2,a2-b2+12+d2-c2,b2-a2+d2-c2) Harshawardhan shastri ================================================== =========== "Julie Holmberg" wrote: What I need to do is have people enter the time they start, what time they leave for lunch, come back from lunch and leave at the end of the day. I want Excel to automatically calculate the number of hours and minutes for each of these so that I can then add up the total number of hours and minutes worked each day. I am having a hard time formatting the cells (do I format them as time or numbers?) and then whenever I try to add or subtract hours, how do you deal with the 12:00 hour, which then seems to mess up any formulas. For example, if someone clocks in at 7 and goes to lunch at 1:30 - how can you create a formula where Excel will get the correct answer of 6:30 minutes worked? HELP! |
|
Thread Tools | |
Display Modes | |
|
|