Converting HH:MM to Minutes in Excel
Date: 06/25/2003 at 10:28:31 From: Saurabh Mehta Subject: Converting Hours:Minutes (HH:MM) to Minutes in Excel Hi, I want to know if there is any easy method (i.e. any formula or a macro) available for converting Hours:Minutes (HH:MM) to Minutes in Excel. For example, 2:32 should be converted to 152.
Date: 06/25/2003 at 12:00:56 From: Doctor Peterson Subject: Re: Converting Hours:Minutes (HH:MM) to Minutes in Excel Hi, Saurabh. You don't need any special formula or macro to do this; it's very easy. Excel stores times and dates as a number of days; for example, when you enter 1:00, meaning one hour, it is stored as the number 0.041666, or 1/24, since an hour is 1/24 of a day. So to convert a time to a number of minutes, all you have to do is convert days to minutes by multiplying by 24*60, and then set the format of the cell to numeric (rather than time, which it otherwise assumes you want). For example, if I put 2:32 into cell A1, and put the formula =A1*24*60 into cell B1, then format B1 as a whole number, I see 152 in B1. Two hours and 32 minutes is the same as 152 minutes. Similarly, you can convert a time to hours by multiplying by 24, or to seconds by multiplying by 24*60*60, or to weeks by dividing by 7. Excel can also do arithmetic on times; so if you want to find the difference in minutes between times stored in A1 and A2, just enter the formula =(A2-A1)*24*60 in B2, and change the format. This subtracts two times, then converts the difference to minutes. Similarly, you can find the number of days between two dates, or show hours and minutes as decimal hours (1.50 instead of 1:30), or display times and dates in custom formats (like "3 hours, 4 minutes, and 48 seconds"). Excel has powerful features, but doesn't always make it clear how easily you can do these things. The key is often in the formatting; you have to tell it, for example, to show the difference between two times not as a date and time (including AM and PM, etc.) but as an elapsed time, or as a number. For more on this, see Excel help under "Calculate the difference between two times," or under "Date and Time functions," as well as "Number format codes." If you have any further questions, feel free to write back. - Doctor Peterson, The Math Forum http://mathforum.org/dr.math/
Search the Dr. Math Library:
Ask Dr. MathTM
© 1994- The Math Forum at NCTM. All rights reserved.