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 8,266 views

## 1 answer to this question.

Assuming your data is located at [D12:D22]

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

• 63,700 points

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

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

## How to import excel file in Oracle SQL live

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

## How to get current time and date in Android

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

## 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

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

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

## Convert Rows to Columns with values in Excel using custom format

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

## IF - ELSE IF - ELSE Structure in Excel

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