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
Math Forum Home || Math Library || Quick Reference || Math Forum Search