How to sum hours and minutes in excel

0 votes

I got a range of cells in an Excel file with values of time like "12:00" (hh: mm), which is stored as plain text, without specific cell formatting. I need to sum up all of the cells, but I got zero when I try the SUM function.
How can I get the total sum of a range of cells in the form "hhh:mm: ss"?

Sep 26, 2022 in Others by Kithuzzz
• 38,000 points
13,550 views

1 answer to this question.

0 votes

Assuming your data is located at [D12:D22]

enter image description here

Try this formulas:

  • To convert a time in the form of text to a serial number use this formula:
    = TIMEVALUE(D12)

  • To add time entered as a number use this formula:
    =SUM(E12:E22) and format the cell as: [hh]:mm:ss

  • To add time entered as text use this formula array:
    = SUM( TIMEVALUE(D$12:D$22) ) and format the cell as: [hh]:mm:ss

enter image description here

answered Sep 27, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

How to format numbers as lakhs and crores in excel/ google spreadsheet when the number could be negative too?

Excel formatting, in my opinion, can only ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 63,600 points
15,386 views
0 votes
0 answers

Data Driven Framework -- how to read and write in excel sheet using Selenium WebDriver with java

I'm using this code to read something, ...READ MORE

Oct 31, 2022 in Others by Kithuzzz
• 38,000 points
703 views
0 votes
1 answer

How to use data validation and 'no filter' in excel

eventually succeeded in resolving this. Effectively, Filter() ...READ MORE

answered Nov 4, 2022 in Others by narikkadan
• 63,600 points
677 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
4,745 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
2,131 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
1,064 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,949 views
0 votes
1 answer

How to get sum of all matches of HLOOKUP in Excel?

Consider: =SUMPRODUCT((A1:E1="apple")*(A2:E2)) To include more ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 63,600 points
2,799 views
0 votes
1 answer

How to Freeze Top Row and Apply Filter in Excel Automation with C#

Try this: // Fix first row workSheet.Activate(); workSheet.Application.ActiveWindow.SplitRow = 1; workSheet.Application.ActiveWindow.FreezePanes ...READ MORE

answered Oct 22, 2022 in Others by narikkadan
• 63,600 points
2,775 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP