Summing up different time formats

[Jennifer]

I have a file in excel where there are five columns that have time formated
as basic numeric. Example: 30 minutes displays as 30, 10 hours displays as
10. How do I show these 5 columns as hours ?
Second question: I have an additional column where the time is displayed as
minutes (on the same worksheet as the five columns). I placed a formula where
it changes it to hours/minutes (= cell/1440) and then format the cell ([h]:mm.

Now I am trying to sum the 6 columns in the worksheet to get the total hours
and is not working. Any suggestions ?

 

[John C]

Suppose columns A & B have hours listed as whole numbers, columns C & E have
minutes listed as whole numbers, and columns D & F have seconds displayed as
whole numbers.
=TIME(A2+B2,C2+E2,D2+F2)
Format this total cell as [hh]:mm:ss

 

[Mike H]

Jennifer

To change a decimal number to time you need to know whether the column
relates to hrs or minutes so assuming A1 refers to minutes, use this and
format as [h]:mm

=TIME(0,A1,0)

Likewise if A1 contained hours use

=TIME(A1,0,0)

The simply sum them in the way you have already indicated.

Mike

 

[Jennifer]

John,
I am receving an error when I try the formula below.The error is: You have
entered to few arguments for this funcation. Here is what I typed:
=time(F5+G5+H5+I5+J5+M5)
F5 thru J5 is formatted as general format
M5 is formatted as h:mm

Should I format the columns differently ?
The columns F5 thru J5 were manually entered
F5 30
G5 3
H5 10
I5 27.5
J5 is 40

All are represent how many hours except F5 this means 30 minutes.

M5 varies but is formatted as h:mm, but as an example we can use 2:17 (which
would be 2 hrs and 17 minutes. Was 137 minutes.

Suppose columns A & B have hours listed as whole numbers, columns C & E have
minutes listed as whole numbers, and columns D & F have seconds displayed as
whole numbers.
=TIME(A2+B2,C2+E2,D2+F2)
Format this total cell as [hh]:mm:ss
--
** John C **

I have a file in excel where there are five columns that have time formated
as basic numeric. Example: 30 minutes displays as 30, 10 hours displays as
10. How do I show these 5 columns as hours ?
Second question: I have an additional column where the time is displayed as
minutes (on the same worksheet as the five columns). I placed a formula where
it changes it to hours/minutes (= cell/1440) and then format the cell ([h]:mm.

Now I am trying to sum the 6 columns in the worksheet to get the total hours
and is not working. Any suggestions ?

 

[John C]

The time function has 3 separate arguments, which must be separated by commas.
=TIME(hours,minutes,seconds)
So, if all of the cells F5, G5, H5, I5, and J5 are hours, and you want to
display the result in M5, then
M5: =TIME(F5+G5+H5+I5+J5,,) or, written another way
M5: =TIME(SUM(F5:J5),,)
If, however, as you stated in your original post, some of the columns are
minutes, and some are hours, say for example, G5 and H5 are hours in whole
numbers, and F5, I5, and J5 are minutes in whole numbers, then you could have
your formula in M5 like so:
M5: =TIME(G5+H5,F5+I5+J5,)
I would format M5 as either [hh]:mm or even [hh]:mm:ss just in case your
time goes over 24 hours.
--
** John C **

John,
I am receving an error when I try the formula below.The error is: You have
entered to few arguments for this funcation. Here is what I typed:
=time(F5+G5+H5+I5+J5+M5)
F5 thru J5 is formatted as general format
M5 is formatted as h:mm

Should I format the columns differently ?
The columns F5 thru J5 were manually entered
F5 30
G5 3
H5 10
I5 27.5
J5 is 40

All are represent how many hours except F5 this means 30 minutes.

M5 varies but is formatted as h:mm, but as an example we can use 2:17 (which
would be 2 hrs and 17 minutes. Was 137 minutes.

Suppose columns A & B have hours listed as whole numbers, columns C & E have
minutes listed as whole numbers, and columns D & F have seconds displayed as
whole numbers.
=TIME(A2+B2,C2+E2,D2+F2)
Format this total cell as [hh]:mm:ss
--
** John C **

I have a file in excel where there are five columns that have time formated
as basic numeric. Example: 30 minutes displays as 30, 10 hours displays as
10. How do I show these 5 columns as hours ?
Second question: I have an additional column where the time is displayed as
minutes (on the same worksheet as the five columns). I placed a formula where
it changes it to hours/minutes (= cell/1440) and then format the cell ([h]:mm.

Now I am trying to sum the 6 columns in the worksheet to get the total hours
and is not working. Any suggestions ?

 

[Jennifer]

Okay works for the columns that have 30 mins, 3 hrs, 20 hrs, but for the
columns that have 40 hrs (listed as general format) and the column that has
27.5 (listed as general format) it does not sum properly. Here are examples
of what I have:

1,650 (formatted general - is 27 hours and 30 minutes) column I
24 (formatted general - 24 minutes) column k

I used the formula in cell O2 = time(,I2+K2,)
my results were 03:54 instead of 27:54.
I did format the cell O2 as [hh]:mm

The time function has 3 separate arguments, which must be separated by commas.
=TIME(hours,minutes,seconds)
So, if all of the cells F5, G5, H5, I5, and J5 are hours, and you want to
display the result in M5, then
M5: =TIME(F5+G5+H5+I5+J5,,) or, written another way
M5: =TIME(SUM(F5:J5),,)
If, however, as you stated in your original post, some of the columns are
minutes, and some are hours, say for example, G5 and H5 are hours in whole
numbers, and F5, I5, and J5 are minutes in whole numbers, then you could have
your formula in M5 like so:
M5: =TIME(G5+H5,F5+I5+J5,)
I would format M5 as either [hh]:mm or even [hh]:mm:ss just in case your
time goes over 24 hours.
--
** John C **

John,
I am receving an error when I try the formula below.The error is: You have
entered to few arguments for this funcation. Here is what I typed:
=time(F5+G5+H5+I5+J5+M5)
F5 thru J5 is formatted as general format
M5 is formatted as h:mm

Should I format the columns differently ?
The columns F5 thru J5 were manually entered
F5 30
G5 3
H5 10
I5 27.5
J5 is 40

All are represent how many hours except F5 this means 30 minutes.

M5 varies but is formatted as h:mm, but as an example we can use 2:17 (which
would be 2 hrs and 17 minutes. Was 137 minutes.

Suppose columns A & B have hours listed as whole numbers, columns C & E have
minutes listed as whole numbers, and columns D & F have seconds displayed as
whole numbers.
=TIME(A2+B2,C2+E2,D2+F2)
Format this total cell as [hh]:mm:ss
--
** John C **

:

I have a file in excel where there are five columns that have time formated
as basic numeric. Example: 30 minutes displays as 30, 10 hours displays as
10. How do I show these 5 columns as hours ?
Second question: I have an additional column where the time is displayed as
minutes (on the same worksheet as the five columns). I placed a formula where
it changes it to hours/minutes (= cell/1440) and then format the cell ([h]:mm.

Now I am trying to sum the 6 columns in the worksheet to get the total hours
and is not working. Any suggestions ?

 

[Glenn]

Okay works for the columns that have 30 mins, 3 hrs, 20 hrs, but for the
columns that have 40 hrs (listed as general format) and the column that has
27.5 (listed as general format) it does not sum properly. Here are examples
of what I have:

1,650 (formatted general - is 27 hours and 30 minutes) column I
24 (formatted general - 24 minutes) column k

I used the formula in cell O2 = time(,I2+K2,)
my results were 03:54 instead of 27:54.
I did format the cell O2 as [hh]:mm

The TIME() function does not understand anything over 24 hours. From the help file:

The decimal number returned by TIME is a value ranging from 0 (zero) to
0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59
(11:59:59 P.M.).

If you have hours in A2 and B2, minutes in C2 and D2, then your result in E2
should be this:

=(A2+B2)/24+(C2+D2)/1440