Time, hours will not total

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I am making a time card, I can get the hours of the day
to total, so I have L20 to L26 that do total to eight
hours a day. L21(monday)to L25(friday) have totaled eight
hours in each cell. I try to total the week in L27 using
=sum(L20:L26) and I get the sum of 16, not 40 hours that
8hrs X 5days should be. I am stuck, can't find the
answers in all the manuals. Anyone know how to do this.

Thanks
there is always someone out there thats got a handle on
this stuff
RMC
(e-mail address removed)
 
Format the total as [h]:mm or multiply by 24

=SUM(L21:L26)*24

and format as general, the latter is handy if you need to make
a calculation to get rates since it is decimal hours..
 
I am using the standard formats for the cells under the
time list under format cells, I have tried HH:mm,
hh:mm:ss and a couple more that are at the first in the
list under the time listing I am not making any special
formats. If you would send me a email I will forward a
copy of this file for your review. My sister is a CFO and
uses excel all the time, but not knowledgeable about
TIME, and she looked at the file and does not see why it
is not working.
 
I tried the *24, this made the cell (L27) go to 0.00. The
cells L20 to L27 are formated for TIME (hh:mm) from the
list in the format cells. No special formats made up by
me!!! L27 should add the 8hrs in each cells L21 to L25.
8hrs X 5days should equal 40 hrs but it comes up to the
total of only 16.
-----Original Message-----
Format the total as [h]:mm or multiply by 24

=SUM(L21:L26)*24

and format as general, the latter is handy if you need to make
a calculation to get rates since it is decimal hours..

--

Regards,

Peo Sjoblom


Rick said:
I am making a time card, I can get the hours of the day
to total, so I have L20 to L26 that do total to eight
hours a day. L21(monday)to L25(friday) have totaled eight
hours in each cell. I try to total the week in L27 using
=sum(L20:L26) and I get the sum of 16, not 40 hours that
8hrs X 5days should be. I am stuck, can't find the
answers in all the manuals. Anyone know how to do this.

Thanks
there is always someone out there thats got a handle on
this stuff
RMC
(e-mail address removed)
Click to expand...


.
Click to expand...
 
Most likely some of the time values are text..
Also I told you to format as general not time,
8:00 times 24 formatted as time returns 0:00 so that is
correct, formatted as general it returns 8.



--

Regards,

Peo Sjoblom


Rick said:
I tried the *24, this made the cell (L27) go to 0.00. The
cells L20 to L27 are formated for TIME (hh:mm) from the
list in the format cells. No special formats made up by
me!!! L27 should add the 8hrs in each cells L21 to L25.
8hrs X 5days should equal 40 hrs but it comes up to the
total of only 16.
-----Original Message-----
Format the total as [h]:mm or multiply by 24

=SUM(L21:L26)*24

and format as general, the latter is handy if you need to make
a calculation to get rates since it is decimal hours..

--

Regards,

Peo Sjoblom


Rick said:
I am making a time card, I can get the hours of the day
to total, so I have L20 to L26 that do total to eight
hours a day. L21(monday)to L25(friday) have totaled eight
hours in each cell. I try to total the week in L27 using
=sum(L20:L26) and I get the sum of 16, not 40 hours that
8hrs X 5days should be. I am stuck, can't find the
answers in all the manuals. Anyone know how to do this.

Thanks
there is always someone out there thats got a handle on
this stuff
RMC
(e-mail address removed)
Click to expand...


.
Click to expand...
Click to expand...
 
Rick,

I have fussed with Time in Excel for years and have found the following,

1. Foramt the cell with TIME type ,13:30
2. Enter all your times in the 24 hour format, 8:00 for 8am and 16:00 for
4pm.
3. subtract the time 8:00 from the 16:00 to get your 8:00 hours.
Here is data I use every day.

8/13 Wednesday 16:45 (clocked in) 19:25 (clocked out) 2:40 total time
8/14 Thursday 16:40 19:00 2:20
8/15 Friday 5:50 8:20 2:30
8/18 Monday 16:45 19:05 2:20
8/19 Tuesday 16:50 19:15 2:25

The 16:40 is 4:40 pm.

I then sum the total time column with
hours =HOUR((SUM(F108:F112))) &
min =MINUTE((SUM(F108:F112)))
TOT MIN =MINUTE(SUM(F108:F112))+(HOUR((SUM(F108:F112)))*60)

I use the Total Minutes to check my self. I then multiply the TOT MIN by
$C$1 (my hourly rate)

Please note that Excel gives the decimal eq. in time.
15 minutes entered becomes .25 for the calulated time.

HTH.

Wayne B
 
Back
Top