Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-17-2020, 01:08 AM
otuatail otuatail is offline add up HH:MM:SS (Office 2007) Windows 7 64bit add up HH:MM:SS (Office 2007) Office 2007
Competent Performer
add up HH:MM:SS (Office 2007)
 
Join Date: Jun 2012
Posts: 246
otuatail is on a distinguished road
Default add up HH:MM:SS (Office 2007)


How to add up a column of HH:MM:SS

I am looking at my talk talk phone bill. I downloaded it as a CSV, re=adjusted the column widths and saved it as an xlsx. The sheet was set up as custom for the times of calls. I tried to convert them to time. I ten hi-lighted all the calls down to below the last and clicked on sum. It doesn't work. I am attaching it for someone to tell me where I am going wrong.

Thanks
Attached Files
File Type: xlsx MyBill.xlsx (11.7 KB, 8 views)
File Type: xlsx MyBill_Dosnt_add_up.xlsx (12.1 KB, 8 views)
Reply With Quote
  #2  
Old 01-17-2020, 04:09 AM
ArviLaanemets ArviLaanemets is offline add up HH:MM:SS (Office 2007) Windows 8 add up HH:MM:SS (Office 2007) Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

In attached, filtered calls are counted and totaled.


For duration, a format is used which allows durations > 24 hours displayed.


Subtotal function used in top of page calculates counts/sums displayed in Table tCallData for displayed records, whatever autofilter conditions are applied to Table tCallData.


You copy data from bill into Table tCallData (Use PasteSpecial > Values to avoid overwriting formats on sheet CallData), and add CallType values for every entry. I think the workbook will cope with calls of full year easily.
Attached Files
File Type: xlsx MyBill.xlsx (17.8 KB, 7 views)
Reply With Quote
  #3  
Old 01-17-2020, 09:21 AM
otuatail otuatail is offline add up HH:MM:SS (Office 2007) Windows 7 64bit add up HH:MM:SS (Office 2007) Office 2007
Competent Performer
add up HH:MM:SS (Office 2007)
 
Join Date: Jun 2012
Posts: 246
otuatail is on a distinguished road
Default

No this is not going to work. I get a spread sheet every month. I can't copy paste and manipulate data into another spread sheet just to get what I want. I need to correct the spread sheet to use it.

It's a nice fancy spread sheet but I just want a quick calculation. There has to be a simpler way

Seems to call functions that are hidden which I don't even understand.
Reply With Quote
  #4  
Old 01-18-2020, 07:39 AM
p45cal's Avatar
p45cal p45cal is offline add up HH:MM:SS (Office 2007) Windows 10 add up HH:MM:SS (Office 2007) Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

There's only one reason I see that the value in cell F87 is wrong; it not summing the right range. The formula is currently:
=SUM(F85:F86)
A better formula would be:
=SUM(F54:F84)
which gives 4 hours, 50 minutes and 4 seconds
If there's a possibility that the time summed exceeds 24 hours you'd be better formatting that cell with custom format:
[h]:mm:ss
with the square brackets enabling the display of hour values greater than 24.


If you select the single cell where you want the sum to appear, away from other cells containing data, then click the sum symbol, you'll get just
=SUM(|)
in the cell with the cursor sitting within the parentheses, do nothing except select the cells you want summing and press Enter on the keyboard.
The only difference from what you were doing is now you're selecting (highlighting) the cells you want to sum after clicking the sum symbol rather than before clicking it.
Reply With Quote
  #5  
Old 01-22-2020, 07:15 AM
otuatail otuatail is offline add up HH:MM:SS (Office 2007) Windows 7 64bit add up HH:MM:SS (Office 2007) Office 2007
Competent Performer
add up HH:MM:SS (Office 2007)
 
Join Date: Jun 2012
Posts: 246
otuatail is on a distinguished road
Default

I managed to solve the problem. In the spreadsheet there were blanks in the time columb. Removing the planks or setting them to 00:00:00 solved the problem.

SIMPLE
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cannot get latest Office Updates for Office 2007, Using Windows 7 Home Premium DLD1100 Office 1 12-01-2022 12:12 AM
Run-time error in VB.NET program that worked under Office XP but quit when upgraded to Office 2007 Dick_in_Dallas Misc 0 03-14-2015 07:07 PM
Proper Procedure for updating Office 2000 to Office 2007 Professional stevetag Office 5 08-03-2012 02:47 AM
Using Korean language dictionary from Office 2007 on Office 2011 doable? kylera Office 0 07-10-2012 02:12 AM
Using Office Premium 2007 Trial with Already Installed Office Home and Student 2007 SME Office 1 10-07-2009 06:51 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:48 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft