XL: Converting a Decimal Number to hh:mm:ss Format in Excel

 Last reviewed: February 2, 1998
Article ID: Q75618

The information in this article applies to:
  • Microsoft Excel 98 Macintosh Edition
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel for Windows, versions 2.1, 3.0, 4.0, 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0, 5.0a
  • Microsoft Excel for OS/2, versions 2.2, 3.0

SUMMARY

The following formula converts decimal minutes to the hh:mm:ss format in Microsoft Excel 

   ="Minutes"/1440
where "Minutes" is a decimal number between 0.0 and 1440.0. The portion of the number to the left of the decimal refers to the number of minutes. The decimal portion of the number refers to seconds. 1439.0 minutes is equal to 23 hours and 59 minutes and converts to "23:59:00". There are 1440.0 minutes in 24 hours. Using the formula shown above will return a serial number. To display the serial number in the appropriate format, choose Number from the Format menu (or, on the Format menu, click Cells, and then select the Number tab) and select hh:mm:ss.

To convert hours and minutes from a decimal number to the hh:mm:ss format use the following formula: 

   ="Hours.Minutes"/24

Example

Use this formula to convert 4.5 minutes to 0:04:30: 

   =4.5/1440
Decimal hours.minutes:

Use this formula to convert 4.5 hours to 04:30:00: 

   =4.5/24

REFERENCES

"Microsoft Excel Function Reference," version 3.0, pages 200, 236, 239

Additional query words:
Keywords : xlformat
Version : WINDOWS:2.1,3.0,4.0,5.0,5.0c,7.0,97; MACINTOSH: 3.0,4.0,5.0,5.0a,98; os/2:2.2,3.0
Platform : MACINTOSH OS/2 WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.