Drexel dragonThe Math ForumDonate to the Math Forum

Ask Dr. Math - Questions and Answers from our Archives
_____________________________________________
Associated Topics || Dr. Math Home || Search Dr. Math
_____________________________________________

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/ 
Associated Topics:
High School Calculators, Computers
Middle School Calendars/Dates/Time

Search the Dr. Math Library:


Find items containing (put spaces between keywords):
 
Click only once for faster results:

[ Choose "whole words" when searching for a word like age.]

all keywords, in any order at least one, that exact phrase
parts of words whole words

Submit your own question to Dr. Math

[Privacy Policy] [Terms of Use]

_____________________________________
Math Forum Home || Math Library || Quick Reference || Math Forum Search
_____________________________________

Ask Dr. MathTM
© 1994-2013 The Math Forum
http://mathforum.org/dr.math/