Excel Convert Decimal Time to Clock Time: A Guide

When working with time durations in Excel, it's essential to understand how the software handles decimal times. By default, Excel will 'roll over' when a duration exceeds 24 hours, displaying it as if it were a clock. However, this can lead to incorrect calculations and confusing results.

To overcome this limitation, you can use square brackets in your time format to display hour durations greater than 24 hours. For example, the custom format 'hh':mm will display times exceeding 24 hours correctly.

Another common issue with Excel time is its default AM/PM display. When calculating time durations that exceed 24 hours, this formatting can be misleading. To remove the AM/PM designation, you can apply a custom number format like h:mm, which displays only the hour and minute components.

Converting Decimal Time to Clock Time

To convert decimal times to clock times in Excel, you need to multiply the time duration by 24. This converts the fractional part of one day into a decimal value that represents hours. For example, 12 hours becomes 0.5, and 18 hours becomes 0.75.

Once you have converted the decimal time to a clock format, you can use it in calculations with hourly rates. However, be aware that Excel will display times exceeding 24 hours using AM/PM notation by default. To avoid this, apply a custom number format or use the 'hh':mm syntax.

Using M Code in Power Query

If you need to convert decimal time to clock time in Power BI, you can use the M code provided below:

This code creates a new column in your table that converts the decimal time to a 24-hour format using the Time.ToText function. The h:nn format displays the hour and minute components without AM/PM notation.

Converting decimal time to clock time is essential when working with time durations in Excel or Power BI. By understanding how Excel handles decimal times and applying custom formats, you can ensure accurate calculations and clear display of your data. Remember to multiply decimal times by 24 to convert them to a clock format and use the 'hh':mm syntax or custom number formats to remove AM/PM notation.

Excel convert decimal time to clock time