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

  • Thread starter Thread starter ChasSquirrel
  • Start date Start date
C

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
 
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...
 
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
 
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.

T. Valko said:
Multiply by 1440

A1 = 221:52 [mm]:ss format

=A1*1440

--
Biff
Microsoft Excel MVP


ChasSquirrel said:
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
Click to expand...
Click to expand...
 
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!

Sheeloo said:
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...


ChasSquirrel said:
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
Click to expand...
Click to expand...
 
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


ChasSquirrel said:
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.

T. Valko said:
Multiply by 1440

A1 = 221:52 [mm]:ss format

=A1*1440

--
Biff
Microsoft Excel MVP


ChasSquirrel said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top