Adding Time

  • Thread starter Thread starter Troy
  • Start date Start date
T

Troy

If I have a column that has these numbers:

0:24
3
0:57
2
1:10
5

How do I make excel add up on the time format numbers?
 
One way

=SUMPRODUCT(--(MOD(A1:A10,1)))

if the integers are hours and you want a time format total

=SUMPRODUCT(--(MOD(A1:A10,1)))+SUMPRODUCT(--(INT(A1:A10)))/24

format [h]:mm
 
Troy

Providing the cells have only times (not dates) and the cells between have
only whole numbers, then

=MOD(SUM(A1:A6),1)

Will work. Don't forget to format the result as [h]:mm if it is likely to
go over 24hrs, or you will get a date result

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

If I have a column that has these numbers:

0:24
3
0:57
2
1:10
5

How do I make excel add up on the time format numbers?
Click to expand...

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Thank you.

This did work but I also am doing a total of numbers
excluding the time format ones. Can you help me on this
one?
-----Original Message-----
Troy

Providing the cells have only times (not dates) and the cells between have
only whole numbers, then

=MOD(SUM(A1:A6),1)

Will work. Don't forget to format the result as [h]:mm if it is likely to
go over 24hrs, or you will get a date result

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

If I have a column that has these numbers:

0:24
3
0:57
2
1:10
5

How do I make excel add up on the time format numbers?
Click to expand...

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)



.
Click to expand...
 
When I originally posted this question I expected to be
able to just throw a IF Statement at the start so IF any
cells in the range of AI7:AI680="1" then MOD(SUM
(B7:B680),1) but it won't work. Can you help me please.
-----Original Message-----
Thank you.

This did work but I also am doing a total of numbers
excluding the time format ones. Can you help me on this
one?
-----Original Message-----
Troy

Providing the cells have only times (not dates) and the cells between have
only whole numbers, then

=MOD(SUM(A1:A6),1)

Will work. Don't forget to format the result as [h]:mm if it is likely to
go over 24hrs, or you will get a date result

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

If I have a column that has these numbers:

0:24
3
0:57
2
1:10
5

How do I make excel add up on the time format numbers?
Click to expand...

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)



.
Click to expand...
.
Click to expand...
 
I figured out how make them sum to the corresponding month
but I still can't sum only whole numbers and not time
formats.
-----Original Message-----
When I originally posted this question I expected to be
able to just throw a IF Statement at the start so IF any
cells in the range of AI7:AI680="1" then MOD(SUM
(B7:B680),1) but it won't work. Can you help me please.
-----Original Message-----
Thank you.

This did work but I also am doing a total of numbers
excluding the time format ones. Can you help me on this
one?
-----Original Message-----
Troy

Providing the cells have only times (not dates) and the cells between have
only whole numbers, then

=MOD(SUM(A1:A6),1)

Will work. Don't forget to format the result as [h]:mm if it is likely to
go over 24hrs, or you will get a date result

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)


Troy wrote:
If I have a column that has these numbers:

0:24
3
0:57
2
1:10
5

How do I make excel add up on the time format numbers?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)



.
Click to expand...
.
Click to expand...
.
Click to expand...
 
It will have a problem with the 3, 2, and 5 because it
will see it as a date, not a time. Example: with the
1900 date system, the 3 will be January 3, 1900, the 2
will be January 2, 1900, and the 5 will be January 5,
1900. It will not see it as time, but a date--even if you
format it for time. It should be put in a time format--if
you mean hours put 3:00, 2:00, and 5:00. It will then add
it correctly.
 
Troy

Again, so long as the times do not also have dates, try

=SUMIF(A1:A7,">=1")

And format as general
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

Thank you.

This did work but I also am doing a total of numbers
excluding the time format ones. Can you help me on this
one?
-----Original Message-----
Troy

Providing the cells have only times (not dates) and the cells
between have only whole numbers, then

=MOD(SUM(A1:A6),1)

Will work. Don't forget to format the result as [h]:mm if it is
likely to go over 24hrs, or you will get a date result

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

If I have a column that has these numbers:

0:24
3
0:57
2
1:10
5

How do I make excel add up on the time format numbers?
Click to expand...

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)



.
Click to expand...
Click to expand...

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Back
Top