This guide will explain using the TEXT function to convert decimal values into the equivalent number of days, hours, and minutes.
With the TEXT function, we can change the way a number appears to the user. Excel comes with several built-in date formatting options that we can use to convert a decimal into various time units.
Let’s take a quick example of using the TEXT function to convert decimals into days, hours, and minutes.
Suppose we have a dataset of historical data that indicates how long it takes for an order to be shipped. Each data point includes a total_time value that equals the total amount of time in hours between the customer placing an order and the courier dropping off the package.
For example, a shipment may take 245.56 hours to reach the customer. To make it easier to make sense of the sheet, you would like to convert this value into days, hours, and minutes. How can we accomplish this in Excel?
With the TEXT and INT functions, you can convert the decimal value in hours to the corresponding number of days, hours, and minutes.
Now that we know when to use the TEXT function, let’s dive into how to use it and work on an actual sample spreadsheet.
A Real Example of Converting Decimal to Days, Hours, and Minutes in Excel
Let’s take a look at a real example of the TEXT function being used in an Excel spreadsheet.
In the example below, we have a list of values in column A that represent duration in hours. Column B converts those values into a more readable format where the days, hours, and minutes of the duration are given.
To get the values in Column B, we just need to use the following formula:
=INT(A2/24) & "d " & TEXT(A2/24;"h\h m\m")
The formula works by first converting the value from hours to days. Dividing the original value by 24 will give you the equivalent value in days. For example, 48 hours divided by 24 will provide you with exactly 2 days. Since this will not always result in a whole number, we wrap the result in an INT to force the result to be an integer.
Next, we’ll use the equivalent value in days as our first argument for the TEXT function. The second argument sets up the formatting for the final result. The characters ‘d’, ‘h’, and ‘m’ retrieve the days, hours, and minutes from a given decimal.
Excel will assume that the decimal refers to a certain number of days. For example, if the input value is 1.5, the ‘d’ will return one day, and the ‘h’ will return 12 hours. Since there are no other values in the minutes place, ‘m’ will return a ‘0’.
In the final result, we also want to include the actual letters ‘h’ and ‘m’. The final results should look something like the following:
1d 23h 11m
To add these letters, we simply add a forward slash after the letter to indicate that it is not a formatting shortcut.
You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try out the TEXT function in Excel, let’s begin writing it ourselves!
How to Convert Decimal to Days, Hours, and Minutes in Excel
This section will guide you through each step needed to start using the TEXT function in Excel to convert decimals to days, hours, and minutes. Use this guide to find out how we can convert the number of hours into days and apply the necessary formatting.
Follow these steps to start using the TEXT function:
- First, select the cell that will contain our
TEXTfunction. In this example, we will start with cell B2.
- Next, we just simply type the equal sign ‘=‘ to begin the function, followed by ‘TEXT. For the first argument, we will divide the number of hours by 24 to convert the value into the number of days. In the second argument, we will have to add a format string. This string will specify how to format the value in the first argument.
- Hit the Enter key to evaluate the function. In the example below, we’ve converted the value of 0.5 hours into 0 days, 0 hours, and 30 minutes.
- Fill in the rest of the column by dragging down the formula in cell B2.
Frequently Asked Questions (FAQ)
- Can we use the ‘d’ formatting with the TEXT function for this method?
For values less than 32 days, we can use the formulaTEXT(A1/24,"d\d h\h m\m")to convert.However, since the formula does not work for values greater than 32 days, it is safer to retrieve the number of days by converting it into an integer usingINTinstead.
This step-by-step guide should be all you need to begin converting decimals into formatted days, hours, and minutes in Excel. Our guide shows how we can simply use the TEXT function to apply formatting to a value representing a certain number of days.
Converting numbers into specific date formats is just one example of what Excel’s formatting can help users with. With so many other Excel functions available, you can surely find a use case that you can apply to your own worksheets.
Are you interested in learning more about what Microsoft Excel can do? Subscribe to our newsletter to find out about the latest guides and tutorials from us.
