How do I set up a time sheet in Excel?

[Julie Holmberg]

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!

 

[HARSHAWARDHAN. S .SHASTRI]

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(b2<a2,a2-b2+12+d2-c2,b2-a2+d2-c2)

Harshawardhan shastri

=============================================================

 

[David Biddulph]

They need to put in 7:00, rather than 7, and preferably 13:30 rather than
1:30, but you can get away with 1:30 PM.
After that, it's straight subtraction. Format the results as [h]:mm and
that will cope with adding up the week's total to more than 24 hours.

At a push you could cope with the 12 hour problem with =MOD(B2-A2,0.5), but
I wouldn't recommend it.

 

[Peo Sjoblom]

Or

=D2-A2-(C2-B2)

--


Regards,


Peo Sjoblom

"HARSHAWARDHAN. S .SHASTRI"

 

[Peo Sjoblom]

Just subtract and format as hh:mm

--


Regards,


Peo Sjoblom

 

[David Biddulph]

I guess you intended =if(b2<a2,b2-a2+12+d2-c2,b2-a2+d2-c2) rather than
=if(b2<a2,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?]

 

[Julie Holmberg]

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?

 

[Peo Sjoblom]

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

 

[David Biddulph]

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.

 

[Julie Holmberg]

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(D14<D13,D14-D13+12+D16-D15,D14-D13+D16-D15)

 

[Peo Sjoblom]

Yikes!

Try


=D16-D13-(D15-D14)

if the start time is before midnight and end after midnight you can use


=MOD(D16-D13-(D15-D14),1)


format result as [h]:mm or h:mm

--


Regards,


Peo Sjoblom

 

[David Biddulph]

If you did indeed read my reply to Harshawardhan's post then you'll see why
=IF(D14<D13,D14-D13+12+D16-D15,D14-D13+D16-D15) won't work.

Did you try =MOD(D14-D13,0.5)+MOD(D16-D15,0.5) ?

But (to say it one more time in case you are still missing it):
"I would stick to my recommendation of using either the 24 hour clock or
specifically saying 1:30 PM, not just 1:30."
Putting in ambiguous times is asking for trouble.