Converting Minutes and Seconds ([mm]:ss) to Decimal

[ChasSquirrel]

I work on the phone and get paid per minute. I keep track of my calls in a
spreadsheet. I have the cell format set to [mm]:ss so it still shows minutes
even when the minutes go over 60. My question is: How do I convert the total
MIN:SEC to the decimal version so I can them mulitply that by .22. I've seen
how to convert it when you are using HOUR:MIN but not MIN:SEC.
for example:
If I have a total of 221:52 i.e. 221 minutes and 52 seconds I want to
convert it to 221.86.
Thanks for your help!
Shirley

 

[Sheeloo]

Excel stores dates and times as number of days from 1/1/1900
so if you convert 1/1/1900 to decimal you will get 1, for 1/2/1900
(2nd-Jan-1900) you will get 2 and so on...

Thus 1 represents 24 hours... So to convert your time to hours in decimal
simply multiply by 24.

Decimal representation of 221:52 is 0.154074074074074 which when multiplied
by 24 will give you 3.697777777778 hours...

 

[T. Valko]

Multiply by 1440

A1 = 221:52 [mm]:ss format

=A1*1440

 

[Conan Kelly]

ChasSquirrel,

Like Sheeloo mentioned:
- 1 day = 1
- 2 days = 2
- a half day (or 12 hours) = 0.5
- 1 hour = 1/24 = 0.41666 (one day divided by 24 hours in a day)
- 1 min = 1/24/60 = 0.00069444 (one day divided by 24 hours divided by
60 minutes in an hour)

Your times will show up as [mm]:ss, but it is stored in XL as a
decimal/fraction of a day.

So, to get from [mm]:ss to decimal minutes, multiply your min/sec cells by
60 minutes in an hour, then by 24 hours in a day.

If cell A1 has 221:52 mins/secs in it, then enter the following formula in
B1 to calculate decimal minutes:

=A1*24*60

Then format cell B1 as a number with decimal places...XL might defalut to
the same time format you are referencing in cell A1. B1 will show 221.87.

HTH,

Conan Kelly

 

[ChasSquirrel]

Thank you! This is just as simple as it can be and works like a charm. I
knew I should have just asked instead of driving myself crazy searching for
the answer.

Multiply by 1440

A1 = 221:52 [mm]:ss format

=A1*1440

--
Biff
Microsoft Excel MVP

I work on the phone and get paid per minute. I keep track of my calls in a
spreadsheet. I have the cell format set to [mm]:ss so it still shows
minutes
even when the minutes go over 60. My question is: How do I convert the
total
MIN:SEC to the decimal version so I can them mulitply that by .22. I've
seen
how to convert it when you are using HOUR:MIN but not MIN:SEC.
for example:
If I have a total of 221:52 i.e. 221 minutes and 52 seconds I want to
convert it to 221.86.
Thanks for your help!
Shirley

 

[ChasSquirrel]

but I don't need the hours in the decimal version of the time. I just want
to have a total number of mins and the seconds represented by the decimal so
I can just multiply that by .22 cents a minute.

just mulitpling the time in mins and secs by 1440 works!

Excel stores dates and times as number of days from 1/1/1900
so if you convert 1/1/1900 to decimal you will get 1, for 1/2/1900
(2nd-Jan-1900) you will get 2 and so on...

Thus 1 represents 24 hours... So to convert your time to hours in decimal
simply multiply by 24.

Decimal representation of 221:52 is 0.154074074074074 which when multiplied
by 24 will give you 3.697777777778 hours...

I work on the phone and get paid per minute. I keep track of my calls in a
spreadsheet. I have the cell format set to [mm]:ss so it still shows minutes
even when the minutes go over 60. My question is: How do I convert the total
MIN:SEC to the decimal version so I can them mulitply that by .22. I've seen
how to convert it when you are using HOUR:MIN but not MIN:SEC.
for example:
If I have a total of 221:52 i.e. 221 minutes and 52 seconds I want to
convert it to 221.86.
Thanks for your help!
Shirley

 

[T. Valko]

You're welcome. Thanks for the feedback!

In case you're wondering:

1440 = 60 minutes per hour * 24 hours = total number of minutes in a day

To convert a time to decimal hours:

A1 = 1:00 PM

=A1*24

Format as General or Number

Result = 13

If you wanted to convert to decimal seconds then you'd multiply be 86400

86400 = 60 seconds per minute * 60 minutes per hour * 24 hours = total
number of seconds in a day

A1 = 0:15:00 h:mm:ss format

=A1*86400

Format as General or Number

Result = 900

--
Biff
Microsoft Excel MVP

Thank you! This is just as simple as it can be and works like a charm. I
knew I should have just asked instead of driving myself crazy searching
for
the answer.

Multiply by 1440

A1 = 221:52 [mm]:ss format

=A1*1440

--
Biff
Microsoft Excel MVP

I work on the phone and get paid per minute. I keep track of my calls
in a
spreadsheet. I have the cell format set to [mm]:ss so it still shows
minutes
even when the minutes go over 60. My question is: How do I convert the
total
MIN:SEC to the decimal version so I can them mulitply that by .22.
I've
seen
how to convert it when you are using HOUR:MIN but not MIN:SEC.
for example:
If I have a total of 221:52 i.e. 221 minutes and 52 seconds I want to
convert it to 221.86.
Thanks for your help!
Shirley