Excel sometimes minutes and seconds, how to sum?

Hover over any data cell and see what the data looks like in the formula bar. If, as in the cell, the display is: **hour**minute**second, then the data is indeed text; And if the time format is displayed, then the data is the time format.

It can be summed directly.

There are many ways to do it if it is text data, but it is recommended to replace the data back to time data first, the method is: select the data region, find and replace, replace the second with none, and replace the hour and minute with the English colon. Then set the cell to the required display format, such as the format of the screenshot

In the end, it is a direct sum.

It is recommended to use real time.

The true time itself is a numerical value, which can be directly added and summed, and the format can be set as needed.

This situation depends on the format of your raw data.

If it's a number.

text(sumproduct(--text(b1:e1,"h""times""m""points""s""seconds""")),"h""times""m""points""s""seconds""")

If it's text.

text(sum(--text(rept("0 hours",if(iserror(find("times",b6:e6)),1,0))&b6:e6,"h""times""m""points""s""seconds""")),"h""times""m""points""s""seconds""")

The second is an array function, a three-key input.

Of course, you can also merge the two formulas, but it's too troublesome, and it's not necessary...

For reference, see figure.

What I said upstairs, replace it first, and then calculate, is a simple method, which is good.

Tools Material: Excel 2010

1. First of all, in the computer, double click to open the ** that needs to be summed, and select the cell that needs to be summed, right-click the mouse, and check the format of the cell to enter.

2. Click the number in the Format Cell, and select Custom, and enter [h]:mm:ss below the type, and click OK.

3. Then find the automatic summation option in ** and click this option.

4. After clicking, select all the cells that need to be summed in **.

5. After selecting, click Enter to sum all the time.

Take what's on the diagram as an example:

Find the summary of minutes first, and then the summary of seconds.

2. D2 cell formula: =-mid(b3,find("minutes",b3)+2,find("seconds",b3)-find("minutes",b3)-2)。

Note the two minus signs after the equal sign, and don't have spaces in between. (I use my mobile phone to type,The input method is always changed to a dash,There's no way I'm in a space)

3. Pull down the formulas in the two columns and summarize them separately in row 8.

4. Enter the formula =C8+INT (D8 60) in C9 and =mod(D8,60) in D9

5. Enter the formula in b9: =c9&"minutes"&d9&"seconds"Finish.

Write the specification in excel format before you can calculate.

00:09:57 means 9 minutes and 57 seconds.

For time-type values, you can sum them directly, for example, if cells b1 to b7 are time, then sum is.

sum(b1:b7)

At the same time, the cell should also be in chronological format.

Are you going to add up all that time like a numerical value? If that's the case, just like adding a value, just sum it with the sum function.

One thing to note, though, is that if your cell is just time, it's fine.

If your cell contains the full year, month, and day, and you just let it appear in the format of time, this summing will not work. However, if this is the case, there is also a way, just subtract the integer part of the cell before summing.

Nothing special. Direct sum(b2:b7).

Mine is in Japanese, you don't have to worry about it. Right-click, Formatting.

As with the summing of normal values, the sum function is used.

text(sum(b:b),"[h]:mm")

Directly =sum(b1:b7)*24 and change the cell format to Numeric.

1. Create a new blank excel and insert the test data, working hours, 236:15, 126:45, 118:36, 90:21, 709:31;

2. B2 cell input function, =if(isnumber(find(."times",a2)),left(a2,find("times",A2:A6)-1),0)*1,Get the number of hours, and fill the cells in column B;

3. C2 cell input function, =if(isnumber(find("points",a2)),if(isnumber(find("times",a2)),mid(a2,find("times",a2)+1,len(a2)-find("times",a2)-1),left(a2,len(a2)-1))*1,0), get the number of minutes, and fill the cells in column c;

4. Enter the function in column F, =sum(i:i)+quotient(sum(j:j),60)&"times"&mod(sum(j:j),60)&"points"to get the sum of all durations.

Method 1: Columns (this method requires the help of auxiliary columns).

Then, the two columns of pure numbers (hours and minutes) after the column are summed respectively Method 2: Formula.

Arbitrary cell input: =sum(value(iferror(left(a:a,find("times",a:a)-1),0)))"hours"

Note: The array formula needs to be terminated by pressing Ctrl+Shift+Enter.

Extract the number of hours and minutes separately.

Resummation weight when the original data is in column A.

b2=if(isnumber(find("times",a2)),left(a2,find("times",a2:a6)-1),0)*1

c2=if(isnumber(find("points",a2)),if(isnumber(find("times",a2)),mid(a2,find("times",a2)+1,len(a2)-find("times",a2)-1),left(a2,len(a2)-1))*1,0)

Pull down together. The result is =sum(b:b)+quotient(sum(c:c),60)&"times"&mod(sum(c:c),60)&"points"

First of all, in Excel, time exists as a decimal. For example, 1 hour is equal to 1 24 in excel1 minute is equal to 1 1440 in excel, which explains that a day is 24 hours * 60 minutes. One second is equal to 1 (24*60*60) in excel.

Finding the sum of minutes and seconds has two meanings:

One means to add hours, minutes, and seconds in time.

You need to use the mod remainder function to multiply the hours, minutes, and seconds by different coefficients to get the remainder to get the corresponding time part, and then add them together.

One meaning is to find the addition of two times. If two data are in the same time format, you can add them together. If the time format is not standard, you need to use the time function to format the time data and add it together.

time function: (hour, minute, second).

The time function syntax has the following parameters:

hour required. 0 (zero) to.

between the number of hours. Anything greater than.

The value will be divided by.

24, and the rest will be counted as hours. For example, time(27,0,0).

time(3,0,0)

Or. Need. 0 to.

, representing minutes. Anything greater than.

will be converted to hours and minutes. For example, time(0,750,0).

time(12,30,0)

Or. Need. 0 to.

in seconds. Anything greater than.

will be converted to hours, minutes, and seconds. For example, time(0,0,2000).

time(0,33,22)

Or. am。Note that in the process of adding time, the time that exceeds 24 hours will be reduced to an integer of 1 and will not be displayed. If you want to display the time greater than 24 hours, please set the format in the cell format to: [h]:mm:ss.