excel convert decimal time to clock time

Using arithmetic operations, they extract the hours, minutes, and seconds from the time and then convert them to respective decimal values in hours or minutes. The HOUR, MINUTE, and SECOND functions are specifically designed to work with the time values. The essential arithmetic operation multiplies the time value by 24 hours or 1440 minutes to convert it into respective decimal hour or decimal minute values. Through this article, I tried to explain various methods to convert the time values to respective decimal values. The CONVERT function assumes that the values in column A are in day format and then converts it to the respective minutes with decimal format as needed. Multiplying the time value by these 1440 minutes will return the decimal representation of the time in minutes.įor this demo, I am using the following dataset. If we convert it to minutes, there are 24 * 60 = 1440 minutes. We all know that there are 24 hours in a day. Through this section, I will focus more on how to convert the time values to decimal minutes. Until now, whatever methods I introduced in previous sections used the time-to-hour conversion. Also read: How to Add Minutes to Time in Excel? Converting Time Values to Minutes Otherwise, the result will just be the number of days rather than the time in decimals. Warning: If you are working on the time values where the date is specified explicitly, you have to separate the date and time components and then try to implement the CONVERT formula shown above. Which after the function is executed looks in decimal format. Since no date is specified explicitly in column A while defining the time values, there are no number of days but time. The formula then converts it to the corresponding number of hours. Excel interprets this value as the number of days from the source date 01-01-1990 00:00:00.

Here, in this formula, the CONVERT function takes the value from cell A2. On the Excel sheet, Go to the Developer menu > click on the Insert dropdown > select the Button (Form control) option. Before that, it also sets the format to “#0.00” so that values only represent two decimals.Ĥ. The code finally goes one column to the right and then writes all the decimal values across cells B2:B11. It then converts the minute and second components to the fraction of the hour from the day by dividing them by, respectively. It separates the hour, minute, and second components from the time values. It uses the For loop to look through the entire range of A2:A11 for time values. This code creates a subroutine named ConvertTimetoDecimal. ' Place the decimal value in the respective cell in column B ' Calculate the decimal value by converting time components to fractions of an hourĭecimalValue = hourValue + (minuteValue / 60) + (secondValue / 3600)

' Extract hour, minute, and second components from the time value ' Loop through each cell in the time range Set timeRange = ThisWorkbook.Sheets('Example 4').Range('A2:A11') ' Replace 'Sheet1' with your sheet name ' Define the range containing time values (A2:A11) Copy and paste the following VBA code inside the blank module and save it using the Save button or keyboard shortcut Ctrl + S. The dataset I will be using for this demo is the one below.ģ. These functions extract the respective components from the time, and then there needs to be some arithmetic operation to give you the decimal formatted time values. The formula is as follows: =INT(A2 * 24) Also read: How to Change Date and Time to Date in Excel? Using the HOUR, MINUTE, and SECOND FunctionsĪnother way you can convert time to decimal is by using the HOUR, MINUTE, and SECOND functions. Note: If you want to see the value as a whole number and not as a decimals, you can effectively use the INT function to show only the part before the decimal of this output. Therefore, the minutes and seconds part of the time value are converted to the decimals, respectively. This multiplication converts the time into the fraction of the 24 hours. To explain this concept better, when you have passed 30 minutes, you have ideally passed the half-hour, which you can also represent as 0.5 in decimals. The multiplication by 24 converts the time value into a fraction of how many parts of the 24 hours or one day are utilized. Excel represents the time value as a fraction of the entire day (24 hours or one day). The logic and working of this formula is simple.

Excel convert decimal time to clock time