How to sum time 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,010 points
6,915 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,420 points

Related Questions In Others

0 votes
1 answer

How to open .xlsx files in MS Excel from VS Code?

Hello, to open xlxs files, or files ...READ MORE

answered Feb 17, 2022 in Others by gaurav
• 23,260 points
2,994 views
0 votes
1 answer

How to import excel file in Oracle SQL live

Hello, there are a few steps You'll ...READ MORE

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

How to get current time and date in Android

In order to get the current date ...READ MORE

answered Feb 23, 2022 in Others by Aditya
• 7,680 points
1,708 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,420 points
3,047 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,670 points
1,237 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,670 points
1,423 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,670 points
697 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
2,330 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,420 points
2,031 views
0 votes
1 answer

How to divide data in excel into 4 columns whose sum is almost equal to 1/4 of the sum of all values/

5049 is the sum of all numbers, ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,420 points
406 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