Adding Time

[Joyce C]

I need to add a column of time in this format (mm:ss). For example: I could
have 5000 minutes and 40 seconds (5000:40). I do not want the minutes to
convert to hours.

 

[Gary''s Student]

Format the cells:

[mm]:ss

 

[Joyce C]

I formatted the cells [mm:ss]. In the first cell I put in 51581:21 with no
problem. Second cell I put in 6:40 and got 400:00. Same thing when I
clicked sum in the third cell.

 

[Gary''s Student]

You still need to type in the full time:

0:6:40
will display as:
06:40

1:13:23
will display as:
73:23

--
Gary''s Student - gsnu200824

I formatted the cells [mm:ss]. In the first cell I put in 51581:21 with no
problem. Second cell I put in 6:40 and got 400:00. Same thing when I
clicked sum in the third cell.

Format the cells:

[mm]:ss

 

[Shane Devenshire]

Hi,

You might want to try this to see what's happening:

if A1 displays 5000:40 in [mm]:ss format and in B1 enter

=MINUTE(A1)

 

[Joyce C]

Maybe I should explain what I am trying to do and you can come up with a
solution. We have telephone invoices that we need to add up the minutes on.
The format we receive them in is mm:ss. For example: one invoice has
51561:42, another has 4267:36 and another has 1952:42. I need to enter these
in a spreadsheet and add them up because our cost is per minute. So, would I
have to convert the 51561 minutes into hours and minutes and then format it
as [mm]:ss and so on?

You still need to type in the full time:

0:6:40
will display as:
06:40

1:13:23
will display as:
73:23

--
Gary''s Student - gsnu200824

I formatted the cells [mm:ss]. In the first cell I put in 51581:21 with no
problem. Second cell I put in 6:40 and got 400:00. Same thing when I
clicked sum in the third cell.

Format the cells:

[mm]:ss

 

[Gary''s Student]

Now I understand.

Don't both with the formatting.

Enter your data with a period instead of a :

1952.42
in the adjacent cell enter:
=INT(A1)+100*(A1-INT(A1))/60
this will display:
1952.7
this is pure minutes rather than minutes and seconds. The values in this
adjacent column can be directly added.
--
Gary''s Student - gsnu200824

Maybe I should explain what I am trying to do and you can come up with a
solution. We have telephone invoices that we need to add up the minutes on.
The format we receive them in is mm:ss. For example: one invoice has
51561:42, another has 4267:36 and another has 1952:42. I need to enter these
in a spreadsheet and add them up because our cost is per minute. So, would I
have to convert the 51561 minutes into hours and minutes and then format it
as [mm]:ss and so on?

You still need to type in the full time:

0:6:40
will display as:
06:40

1:13:23
will display as:
73:23

--
Gary''s Student - gsnu200824

I formatted the cells [mm:ss]. In the first cell I put in 51581:21 with no
problem. Second cell I put in 6:40 and got 400:00. Same thing when I
clicked sum in the third cell.

:

Format the cells:

[mm]:ss

 

[Pete_UK]

You can enter them directly as:

0:51561:42, 0:4267:36, and 0:1952:42

i.e. with a leading 0: meaning zero hours, or you can enter them as:

51561:42.0, 4267:36.0, and 1952:42.0

i.e. with a trailing .0 meaning zero fractions of a second. Excel will
then take these in its own time format (fractions of a 24-hour day)
and display them according to how you have formatted the cell.

Hope this helps.

Pete

Maybe I should explain what I am trying to do and you can come up with a
solution.  We have telephone invoices that we need to add up the minutes on.  
The format we receive them in is mm:ss.  For example: one invoice has
51561:42, another has 4267:36 and another has 1952:42.  I need to enterthese
in a spreadsheet and add them up because our cost is per minute.  So, would I
have to convert the 51561 minutes into hours and minutes and then format it
as [mm]:ss and so on?

You still need to type in the full time:

0:6:40
will display as:
06:40

1:13:23
will display as:
73:23

I formatted the cells [mm:ss].  In the first cell I put in 51581:21with no
problem.  Second cell I put in 6:40 and got 400:00.  Same thing when I
clicked sum in the third cell.
:
Format the cells:
[mm]:ss

- Show quoted text -