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.

Format the cells:

[mm]:ss
--
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.

"Gary''s Student" wrote:

Format the cells:

[mm]:ss
--
Gary''s Student - gsnu200824

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


"Joyce C" wrote:

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" wrote:

Format the cells:

[mm]:ss
--
Gary''s Student - gsnu200824

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)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Joyce C" wrote:

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.

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?

"Gary''s Student" wrote:

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


"Joyce C" wrote:

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" wrote:

Format the cells:

[mm]:ss
--
Gary''s Student - gsnu200824

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


"Joyce C" wrote:

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?

"Gary''s Student" wrote:

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


"Joyce C" wrote:

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" wrote:

Format the cells:

[mm]:ss
--
Gary''s Student - gsnu200824

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

On Jan 6, 8:29*pm, Joyce C wrote:
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?



"Gary''s Student" wrote:
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

"Joyce C" wrote:

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" wrote:

Format the cells:

[mm]:ss
--
Gary''s Student - gsnu200824- Hide quoted text -

- Show quoted text -