Basic Formula question

[Matthew]

I'm trying to set up a simple employee time sheet and I want to list each
day showing the employee working say "8 to 6" but then at the end of the
week have excel total up the hours. But I'm not sure how to get the cell
showing "8 to 6" to translate into a numerical value so I can get it to
total up at the end of the week.

 

[Fred Smith]

I would set up two columns: Start time and End time. Then hours worked is:
=(EndTime - StartTime) * 24

Regards
Fred

 

[Roger Govier]

Hi Matthew

You would be better off having 12 columns (assuming 5 working days, 14 if
you have 6 working days).

Name, Start Time, End Time (5 times over to cover 5 working days) then Total
Then format columns B:K as Custom hh:mm AM/PM
Format column L as Custom [hh:mm]
Enter your start and finish times each day in the relevant column.

Then in L2 enter
=SUMPRODUCT((MOD(COLUMN(B2:K2),2)=1)*B2:K2)-
SUMPRODUCT((MOD(COLUMN(B2:K2),2)=0)*B2:K2)

Copy down as required
--
Regards
Roger Govier

I'm trying to set up a simple employee time sheet and I want to list each
day showing the employee working say "8 to 6" but then at the end of the
week have excel total up the hours. But I'm not sure how to get the cell
showing "8 to 6" to translate into a numerical value so I can get it to
total up at the end of the week.


__________ Information from ESET Smart Security, version of virus
signature database 4828 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4828 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Matthew]

I'm trying to set up a simple employee time sheet and I want to list each
day showing the employee working say "8 to 6" but then at the end of the
week have excel total up the hours. But I'm not sure how to get the cell
showing "8 to 6" to translate into a numerical value so I can get it to
total up at the end of the week.

Ok scratch my first question... after thinking about it I've reworked my
time sheet, but I am now stuck again. For each day of the week I have an in
and an out column (i.e. in at 7am out at 4pm) and then I have a total number
of hours worked in the week column at the end. Every employee takes a 30
minute lunch so I came up with a basic formula for each day that is
=(12-B4)+(C4-0.5) to tell me how many hours they worked in a single day but
I'm not sure of how to total this up in the hours worked column and I'm also
not sure how to account for days they don't work (which will vary from week
to week)

 

[Fred Smith]

It would be useful if you told us what is in B4 and C4. One is the start
time, and one the end time?

You will also have to explain how =(12-B4)+(C4-0.5) calculates the hours
worked. Are you entering time without regards to am and pm? If so will this
always work for you? Why not enter the time properly (ie, 4:00 pm or 16:00,
which you can display any way you like).

It seems to me you've done the hard part, and summing hours and days not
worked are the easy part. Surely for days off, the hours worked are 0, and
therefore don't change the total. To sum hours worked, just use the sum
function, as in:
=Sum(c2:c30)

Regards,
Fred

 

[Matthew]

It would be useful if you told us what is in B4 and C4. One is the start
time, and one the end time?

You will also have to explain how =(12-B4)+(C4-0.5) calculates the hours
worked. Are you entering time without regards to am and pm? If so will
this always work for you? Why not enter the time properly (ie, 4:00 pm or
16:00, which you can display any way you like).

It seems to me you've done the hard part, and summing hours and days not
worked are the easy part. Surely for days off, the hours worked are 0, and
therefore don't change the total. To sum hours worked, just use the sum
function, as in:
=Sum(c2:c30)

Sorry about that... I posted this before I saw your initial reply. B4 and C4
are indeed start and stop times for the first day of a 7 day week, and the
only reason I did not put proper time is because the manager wanted 2 weeks
on one page so I was trying to keep the cell info to a minimum by simply
putting a start time of 8 and an end time of 6.

I think I have a basic understanding now after reading through the replies
to set it up though I do have a few snags. In terms of days off the manager
actually wanted to type "off" in the fields... but i can work around this by
just telling him it is not possible. But on days the employees do work they
are expected to take a 30 minute lunch... the only work around I can think
of is to add one more column that is total days worked and then, if the
first day's In of a 7 day week is B4, tag the end of my total column formula
with -(P4*.5) ?

Thank you very much for the help! Although I can find a lot of basic
tutorials for excel on the web, I sometimes find it difficult to locate one
for a specific task.

 

[Roger Govier]

Hi Matthew

From this, I am assuming your days of the week go down the column.
Also, you are entering numbers for the hour of start, not Time values

So with data in B2:C7 the following formula will give the total for the week
=SUMPRODUCT((B2:B7<>"")*11.5)-SUM(B2:B7)+SUM(C2:C7)
--
Regards
Roger Govier

Ok scratch my first question... after thinking about it I've reworked my
time sheet, but I am now stuck again. For each day of the week I have an
in and an out column (i.e. in at 7am out at 4pm) and then I have a total
number of hours worked in the week column at the end. Every employee takes
a 30 minute lunch so I came up with a basic formula for each day that is
=(12-B4)+(C4-0.5) to tell me how many hours they worked in a single day
but I'm not sure of how to total this up in the hours worked column and
I'm also not sure how to account for days they don't work (which will vary
from week to week)


__________ Information from ESET Smart Security, version of virus
signature database 4828 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4828 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com