Adding total hours

[Anthony J. Bertorelli]

I have already learned to formula to calculate the difference between two
times.

=TEXT(H6-G6,"h:mm")

When using this formula, I can clock in at 12:30 AM and clock out at 5:45
PM, and the formula gives me a total time of 17:15 (17 hours and 15
minutes).

Date From To Mileage Time
Beginning Ending Trip In Out Total
04/22/04 67,527 67,536 9 10:15 AM 1:15 PM 3:00
04/22/04 67,537 67,566 29 2:15 PM 5:30 PM 3:15
04/23/04 67,584 67,604 20 10:00 AM 11:45 AM 1:45
04/23/04 67,615 67,636 21 12:00 PM 2:00 PM 2:00
04/24/04 67,654 67,674 20 3:15 PM 4:22 PM 1:07
04/24/04 67,680 67,699 19 5:15 AM 6:00 AM 0:45
04/26/04 67,710 67,745 35 10:30 AM 12:22 PM 1:52
04/28/04 67,791 67,816 25 9:40 AM 10:30 AM 0:50
04/28/04 67,816 67,846 30 11:45 AM 1:30 PM 1:45
04/30/04 67,925 67,951 26 2:00 PM 4:00 PM 2:00


What I want to do is total up everything in the "Total" column. I have
tried using the =SUM function, but that does not seem to work.

My apologies if this topic has been discussed before. When it comes to time
calculation, I am a certified dunce.

 

[Dave Hawley]

Hi Anthony

You have been given some poor advise with
=TEXT(H6-G6,"h:mm")

As the result would be Text and NOT a true time. To subtract times
simply use; =H6-G6 and format this cell h:mm

To add up times, which exceed 24 hours use and cell formatted as [h]:mm

Have a read of this page
http://www.ozgrid.com/Excel/ExcelDateandTimes.htm to learn the all
important Dates and Times in Excel.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Jerry W. Lewis]

I would use
=IF(H6<G6,1+H6-G6,H6-G6)
as that will also let you clock in before midnight and out after
midnight. It also leaves the result numeric, which can be formatted as
time. As Dave noted, you can then just sum the times with an [h]:ss
format to get the total time.

Jerry

 

[Dave Hawley]

Good point Jerry. It can be shortened to

=IF(H6<G6,1+H6-G6,H6-G6)

But as Jerry states, it's a good idea to use a formula like this to
account for 2 dates and times on different days.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Dana DeLouis]

Another option might be:

=H6-G6+(H6<G6)

I would prefer to give a range name to the Time Columns, then use the
following in each Total cell:

=Out-In+(Out<In)