Add up hours

S

Smiley78

I have put in the start time 09:30 and when i quit working 18:15 and made a
formula =SUMMA(D4-C4) so that i get how many hours and minutes i work that
day. But how do i add all hours and minutes for each day so i can see how
much i worked whole month?
 
P

Peo Sjoblom

=D4-C4

no need for SUMMA when you do subtraction


then if you want to sum the whole month, assume the days are in E4:E34

=SUMMA(E4:E34)


now here's the trick, custom format the result as

[tt]:mm:ss


or else you won't display past 24 hours


--


Regards,


Peo Sjoblom
 
C

Chip Pearson

If you put =D4-C4 in column E, for example, you can use a formula like
=SUM(E4:E34) to sum the times in column E. Excel will "roll over" at 24
hours when displaying the result. Thus, thirty hours, 30:00:00 will be
displayed as 6:00:00 (30 hours - 24 hours = 6 hours). To prevent this and
force Excel to display the number of hours, (e.g., 30 hours displayed as
30:00:00) use a custom number format of [hh]:mm or [hh]:mm:ss. The square
brackets [ ] prevent Excel from rolling over at 24 hours. (Note that this
affects only the displayed value, not the actual value of the cell.
Regardless of the display format, thirty hours is the actual value of the
cell.)

If you are going to use the total number of hours in a calculation (e.g.,
Income = TotalHours * Rate), you must multiply the total hours by 24. For
example,

=24*TotalHours*Rate

Format that cell for General or Numberic, not Time.

See www.cpearson.com/Excel/DateTime.htm for much more information about
working with dates and times in Excel.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
R

RichardSchollar

Hi

Just add up all your days' formulas like

=SUM(E4:E26)

assuming they are in E24:E26. What you will probably want to do is to
format this formula cell with a custom number format of:

[h]:mm

So that you get the value in total hours/minutes (ie this number
format will display 2 days, 6 hours as:

54:00

Hope this helps!

Richard
 
Top