I have a spreadsheet that I track employees hours worked I would like to
have it total the number of hours for each day in a cell.

example they worked from 8:00 AM to 3:00 PM total hours is 7

In my cell I enter 800-1500 and manually count the number of hours for each
day.

Anyway to do this in a formula?

Thanks

Tom

Hi,
It's probably easiest to have Start and Finish time in separate
cells: assume
Start in A2, Finish in B2 then in C2 put:

=(B2-A2)+(B2<A2)*24 ' Hours worked

This allows for say 22:00 start and 08:00 finish. Format all cells as "hh:mm"

Copy this formula down column C as appropriate.

HTH

"KeyWest JetSki" wrote:

I have a spreadsheet that I track employees hours worked I would like to
have it total the number of hours for each day in a cell.

example they worked from 8:00 AM to 3:00 PM total hours is 7

In my cell I enter 800-1500 and manually count the number of hours for each
day.

Anyway to do this in a formula?

Thanks

Tom

Could you use two cells for the hours worked? One for time started and
the adjacent cell for time finished. Then have the next cell calculate
the hours worked simply as the difference / 100

ie.

Start | End | Hours Worked
800 | 1500 | =(B2-A2)/100

HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=379051

If you use times like 800 & 1500
than = (1500 - 800)/100 = 7.0

Say start time is in A1 and end time is B1
Total Hours (in cell C1) = (B1-A1)/100

You could also enter date/time numbers and do
something similar.

--
steveB

Remove "AYN" from email to respond
"KeyWest JetSki" wrote in message
...
I have a spreadsheet that I track employees hours worked I would like to
have it total the number of hours for each day in a cell.

example they worked from 8:00 AM to 3:00 PM total hours is 7

In my cell I enter 800-1500 and manually count the number of hours for
each day.

Anyway to do this in a formula?

Thanks

Tom

Tom,

First set up 3 colums Labeled "Start Time", "Stop Time", and "Hour
Worked", or something to that effect. For the cells under "Start" an
"Stop" to Time ( in the menu Format/Cell/Number, or rightclick in cel
then Number tab. In "Hours Worked" column's first cell enter (assum
Start's first cell is a3 and Stop's is b3) "=b3-a3" Copy this row dow
to as many rows as you like.

Hope this helps,
Denni

--
dchil
-----------------------------------------------------------------------
dchill's Profile: http://www.msusenet.com/member.php?userid=6
View this thread: http://www.msusenet.com/t-187053551

If you must input all in one cell

=(TIME(MID(A1,6,2),MID(A1,8,2),0)-TIME(MID(A1,1,2),MID(A1,3,2),0))*24

--
HTH

Bob Phillips

"KeyWest JetSki" wrote in message
...
I have a spreadsheet that I track employees hours worked I would like to
have it total the number of hours for each day in a cell.

example they worked from 8:00 AM to 3:00 PM total hours is 7

In my cell I enter 800-1500 and manually count the number of hours for
each
day.

Anyway to do this in a formula?

Thanks

Tom

Also:

If A1 = start time
B1 = stop time
C1 = Total time

Use Ctrl + Shift + ;
This will enter the current time in a cell

Do this in A1 at the beginning of the shift
And do this in B1 at the end of the shift
(these can be edited after)

In C1 = B1-A1
format as hh:mm

--
steveB

Remove "AYN" from email to respond
"KeyWest JetSki" wrote in message
...
I have a spreadsheet that I track employees hours worked I would like to
have it total the number of hours for each day in a cell.

example they worked from 8:00 AM to 3:00 PM total hours is 7

In my cell I enter 800-1500 and manually count the number of hours for
each day.

Anyway to do this in a formula?

Thanks

Tom

Thanks all

One other what about this time frame

930 2000 I get 10.70 I thought it would be 10.5

I used this one
Start | End | Hours Worked
800 | 1500 | =(B2-A2)/100

any ideas?

"STEVE BELL" wrote in message
news:yTDre.3829$yw4.3119@trnddc09...
Also:

If A1 = start time
B1 = stop time
C1 = Total time

Use Ctrl + Shift + ;
This will enter the current time in a cell

Do this in A1 at the beginning of the shift
And do this in B1 at the end of the shift
(these can be edited after)

In C1 = B1-A1
format as hh:mm

--
steveB

Remove "AYN" from email to respond
"KeyWest JetSki" wrote in message
...
I have a spreadsheet that I track employees hours worked I would like to
have it total the number of hours for each day in a cell.

example they worked from 8:00 AM to 3:00 PM total hours is 7

In my cell I enter 800-1500 and manually count the number of hours for
each day.

Anyway to do this in a formula?

Thanks

Tom

You have come across a fundamental problem with trying to add/subtract
times that have been entered in a general format.

This cell formula is actually calculating 2000 - 930 which gives you
1070 then dividing that by 100 to give 10.7.
The formula doesn't take into account that it is dealing with times.

You can use Bob's formula posted earlier:

=(TIME(MID(A1,6,2),MID(A1,8,2),0)-TIME(MID(A1,1,2),MID(A1,3,2),0))*24

But for that to work you will have to enter all your times with 4
digits (ie. 9:00 am = 0900 NOT 900). It also requires that the times
be entered without any spaces between the respective times and the
dash.

Another solution would be to use:

=(TIME(MID(B1,1,LEN(B1)-2),RIGHT(B1,2),0)-TIME(MID(A1,1,LEN(A1)-2),RIGHT(A1,2),0))*24

with this formula you should be able to enter your times as 0900 or 900
with Start Time in columnA and End Time in columnB.

HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=379051

If you use 930 than 1000 - 930 = 70

9:30 is equivalent to 9.5

Got to be careful when converting hh:mm to numbers
0:06 equals 0.1
0:15 equals 0.25

I have an involved Change macro in one of my sheets that transforms these
numbers
930 becomes 9:30 AM
2130 becomes 9:30 PM

Let me know if you are interested...

--
steveB

Remove "AYN" from email to respond
"KeyWest JetSki" wrote in message
...
Thanks all

One other what about this time frame

930 2000 I get 10.70 I thought it would be 10.5

I used this one
Start | End | Hours Worked
800 | 1500 | =(B2-A2)/100

any ideas?

"STEVE BELL" wrote in message
news:yTDre.3829$yw4.3119@trnddc09...
Also:

If A1 = start time
B1 = stop time
C1 = Total time

Use Ctrl + Shift + ;
This will enter the current time in a cell

Do this in A1 at the beginning of the shift
And do this in B1 at the end of the shift
(these can be edited after)

In C1 = B1-A1
format as hh:mm

--
steveB

Remove "AYN" from email to respond
"KeyWest JetSki" wrote in message
...
I have a spreadsheet that I track employees hours worked I would like to
have it total the number of hours for each day in a cell.

example they worked from 8:00 AM to 3:00 PM total hours is 7

In my cell I enter 800-1500 and manually count the number of hours for
each day.

Anyway to do this in a formula?

Thanks

Tom

Not with my suggestion.

--
HTH

Bob Phillips

"KeyWest JetSki" wrote in message
...
Thanks all

One other what about this time frame

930 2000 I get 10.70 I thought it would be 10.5

I used this one
Start | End | Hours Worked
800 | 1500 | =(B2-A2)/100

any ideas?

"STEVE BELL" wrote in message
news:yTDre.3829$yw4.3119@trnddc09...
Also:

If A1 = start time
B1 = stop time
C1 = Total time

Use Ctrl + Shift + ;
This will enter the current time in a cell

Do this in A1 at the beginning of the shift
And do this in B1 at the end of the shift
(these can be edited after)

In C1 = B1-A1
format as hh:mm

--
steveB

Remove "AYN" from email to respond
"KeyWest JetSki" wrote in message
...
I have a spreadsheet that I track employees hours worked I would like to
have it total the number of hours for each day in a cell.

example they worked from 8:00 AM to 3:00 PM total hours is 7

In my cell I enter 800-1500 and manually count the number of hours for
each day.

Anyway to do this in a formula?

Thanks

Tom