Excell Newbie Quesion Please Help

[KeyWest JetSki]

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

[bhofsetz]

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

[Toppers]

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
>
>
>

[STEVE BELL]

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" <[email protected]> wrote in message
news:%[email protected]...
>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
>

[STEVE BELL]

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" <[email protected]> wrote in message
news:%[email protected]...
>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
>

[Bob Phillips]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>

[dchill]

Tom,

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

Hope this helps,
Dennis


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

[KeyWest JetSki]

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" <[email protected]> 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" <[email protected]> wrote in message
> news:%[email protected]...
>>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
>>
>
>

[bhofsetz]

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

[STEVE BELL]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>>>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
>>>
>>
>>
>
>

[Bob Phillips]

Not with my suggestion.

--
HTH

Bob Phillips

"KeyWest JetSki" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> 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" <[email protected]> wrote in message
> > news:%[email protected]...
> >>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
> >>
> >
> >
>
>