| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Minutes and Seconds ([mm]:ss) to Decimal
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Minutes and Seconds ([mm]:ss) to Decimal
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" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Minutes and Seconds ([mm]:ss) to Decimal
Multiply by 1440
A1 = 221:52 [mm]:ss format =A1*1440 -- Biff Microsoft Excel MVP "ChasSquirrel" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Minutes and Seconds ([mm]:ss) to Decimal
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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Minutes and Seconds ([mm]:ss) to Decimal
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" wrote: Multiply by 1440 A1 = 221:52 [mm]:ss format =A1*1440 -- Biff Microsoft Excel MVP "ChasSquirrel" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Minutes and Seconds ([mm]:ss) to Decimal
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" wrote: 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" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Minutes and Seconds ([mm]:ss) to Decimal
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" wrote in message ... 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" wrote: Multiply by 1440 A1 = 221:52 [mm]:ss format =A1*1440 -- Biff Microsoft Excel MVP "ChasSquirrel" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Converting decimal fractions of minutes to seconds | Excel Worksheet Functions | |||
| converting seconds into minutes | Excel Worksheet Functions | |||
| Converting hours:minutes:seconds to just minutes | Excel Worksheet Functions | |||
| converting Minutes to Seconds | Excel Discussion (Misc queries) | |||
| converting seconds into minutes | Excel Worksheet Functions | |||
Linear Mode
