Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have time values in two cells in an Excel sheet and I need the difference of the times in minutes.

The cells in the Excel are like below:

   A        B        C
1  6:38     8:23     1:45:00

A is the start time
B is the end time
C is the time difference (B1-A1) which is displayed in hh:mm:ss format

A and B cols are formatted as custom hh:mm and cell C is formatted as custom hh:mm:ss.

But I need to get in the value in cell C1 as 105 instead of 1:45:00.

Could you please help in displaying time only in minutes and giving the right format?

share|improve this question

migrated from stackoverflow.com Dec 29 '09 at 17:31

This question came from our site for professional and enthusiast programmers.

up vote 12 down vote accepted

Just set the cell to be shown in numerical format and it will show a decimal number.

The integer part corresponds to the date value (i.e. the day) and the decimal part to the time of the day.

If you multiply the numerical value of 1:45 (which is 0,07) by 24x60 (the number of hours in a day and the number of minutes in an hour) you will obtain the value in minutes of your cell (105).

share|improve this answer

I think this will work:

  • A1 6:23
  • B1 8:23
  • C1 =(B1-A1)*24*60

Just make sure you format C1 as "General"

share|improve this answer

Just format your cell as [mm].

The square brackets around the mm tell Excel to show the total rather than what would be shown if you took the total hours off. The brackets also force Excel to interpret mm as minutes, not as the month. (Without the brackets, m and mm refer to the month if not preceded by h or hh for hours or followed by ss for seconds.)

So, where 1:45:00 with format mm:ss would ignore the hours and show 45:00, both [m]:ss and [mm]:ss will show as 105:00, and both [m] and [mm] would show 105 (where m and mm would show 12, being the month of Excel's default date, which is December 31, 1899).

This also works for hours and seconds. Like 27:10:59 with h:mm would show 3:10, but both [h]:mm and [hh]:mm show 27:10, while both [h] and [hh] show 27. And s would show 59, but [s] gives 97859.

share|improve this answer

Just format your C cell as hh:mm.

share|improve this answer

As some people have already said, If you multiply the numerical value of 1:45 (which is 0,07) by 24x60=1440 (the number of hours in a day and the number of minutes in an hour) you will obtain the value in minutes of your cell (105). Short version: C1 =(B1-A1)*1440

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["                    <li><a href=\"//area51.stackexchange.com\" title=\"proposing new sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2016 Stack Exchange Inc; user contributions licensed under <a href=\"https://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a> \r\n                with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2016.5.27.3620\r\n            </div>\r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Super User works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\r\n    </noscript>\r\n<script>va"], "copyright__copyright__copyright": [" this answer\n\nAs some people have already said, If you multiply the numerical value of 1:45 (which is 0,07) by 24x60=1440 (the number of hours in a day and the number of minutes in an hour) you will obtain the value in minutes of your cell (105). Short version: C1 =(B1-A1)*1440\n\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}