Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Time in Excel and Convert decimal number to Time

Time in Excel

In this tutorial I will try to show action in Excel, related to the calculation of the time, hours and seconds. This example relates to the 24-hour period (1 day). In Excel, there are certain functions that are related to the elements of time (TIME, TIMEVALUE, HOUR, MINUTE, SECOND) and syntax specified function note below.
  • = TIME (hour, minute, second)
  • = TIMEVALUE (time_text)
  • = HOUR (serial_number)
  • = MINUTE (serial_number)
  • = SECOND (serial_number)
By default the time in Excel is entered numerically with a colon (1:25:18 - "hh:mm:ss"). Time display in the cell determines the form (format) by right clicking on the cell and the pop-up menu, select "Format Cells" You will see a dialog box as shown below. After the election the category of "Time" in the right part of the window appearing of the available types of the time in the cell. If you want to display the time over 24 hours then select the type as shown below (37:30:55)

Note! These are my ideas and thoughts, and it is certain that there are simpler but also more complex solutions. However, you understand this tutorial as a guideline for the idea of solving the problem of converting hours, minutes or time entry in Excel cell. Also, you see a formula in Excel related to work with time functions.

Displaying the time or hours as Time Format in Excel

12-hours time in Excel of AM-PM

All users of Excel who work with the 24 hours time (AM/PM) can choose from one of the countries in which such a time is used. As the picture below.

24-hours format time in Excel

Custom time format in Excel cell

If you want more options of displaying time in an Excel cell, then choose the category "Custom" in the zone "Type" select one of the formats themselves or define your own time format.

Custom time format in Excel cell


Enter the Time or Hours, as 'Time' format in Excel - Convert hours to decimal number

When the correct time in the cell, Excel automatically displays the "Time format". So when you type 1:30 Excel will automatically set the "Time format". This form of time is in fact the serial number (serial_number) for the respective time that is not visible in the "Time format". In the picture below you see in column 'A/F' time entered in the "Time format". In the column 'B/G' you note the serial numbers for the respective time (hours). This display hours as the serial number, you can have by selecting all the hours in the "Format Cells" dialog box select the type of the "General". Further note that, for example, 6:00 am, in fact 0.25/day, or one fourth of the day (24/4=6)
Column 'C/H' you note the decimal representation of hours in column 'A/F. The decimal number you get by multiplying the respective time (hour) in column 'A/F' with 24th
You Notice in the picture below that "one day" has 24 hours (column K) as well as other values in minutes and seconds.

Enter the time or hours, as the Time Format in Excel

Columns 'D/I' in the picture above you see the displayed minutes as decimal number. To make the time (hours) display in minutes, enter the following formula.

The formula in cell 'D2' is next:

=A2*60*24

Converting minutes that are entered as a decimal value

If you make calculations with the time shown in minutes (see figure below) then the information above applies here. Excel every time seen as a serial number.
In columns 'A/F' you note recording time as minutes in the "Time format". In columns 'B/G' you note the serial numbers for each such minute in columns 'A/F'. In columns 'C/H' you note the entered integer minutes for any time of the columns 'A/F'.

The formula in cell "C2" is the following:

=MINUTE(A2)

In columns 'D/I' You notice decimal number for each respective minute.

The formula in cell 'D2' is as follows:

=C2/60
Displaying the serial numbers and decimal values for Excel while containing minute

example:
If you want the time in 'Time format' converted to minutes then multiply the time with 1440 (for 1 day = 1440 minutes)

1:37 h => 97 minutes (the format of the cell that contains only minute = General)

=A1*1440
=HOUR(A1)*60+MINUTE(A1)+SECOND(A1)/60


If it comes to a second then the following formula

=A1*86400
=HOUR(A1)*3600+MINUTE(A1)*60+SECOND(A1)

Converting minutes to excel time, serial_number and hours as text

If you have an entry in the form of a number of minutes, and you want to convert you see the image below. You notice in the 'A' column minute shown as numbers. The 'C' column minute are converted at the time as text with the displayed units of measurement time. The 'E' column minutes from 'A' column are converted into time and shows the serial number. And at the end of the 'G' column minute are converted into time format (hours:minutes or h:mm).

Converting a decimal number entry, minutes in the time format of Excel

In order to enter the number of minutes (picture above) to convert at a time that will be displayed as text with time units use the following formula.

The formula in cell "C2" is the following:

=TEXT(TRUNC(A2/60);"00")&" h "&TEXT(A2-TRUNC(A2/60)*60;"00")&" min"

The formula in cell 'E2' is as follows: The cell is formatted as 'General' to show the serial_number for the respective time

=TIMEVALUE(TRUNC(A2/60)&":"&TEXT(A2-TRUNC(A2/60)*60;0))

The formula in cell 'G2' is as follows: In this cell is the same formula as in the cell 'E2' but the cell is formatted as 'Time' to show the respective time

Converting time entered as text in Excel time

In the picture below we have a conversion time that is written as text with a potential measurement units. Text record time converted to Excel time a cell formatted as "Excel Time '

The formula in cell 'C6' and 'C7' is as follows. This cell is formatted as Time category and was selected format Type 37:30:55 (see the first image of this tutorial). The cell 'C7' is formatted as Custom Format, Type: d\dh\h mm "min" (see the third picture of this tutorial). This record in the cell 'C7' is not text.

=SUM(C2:C5)

Furthermore, in the cell 'A11' is entered time as Text "2d 2h 15min". The cell 'B11' is a formula that the text of the 'A11' converted in time but formatted as text.

The formula in cell "B11" is the following:

=MID(A11;1;1)*24+MID(A11;4;1)&":"&TRIM(MID(A11;6;3))

The formula in the cell "C11" is as follows: The cell is formatted as an Excel Time (hh:mm:ss). This formula converts time as a text in the real time Excel.

=VALUE(B11)

Furthermore, the picture below you see the entered date and time as text in a cell 'A16'. This case is often used when you copy the date and time from the Internet or any other document. The cell 'B16' is a formula that converts the text from the cell 'A16' in the Excel serial_number as an argument for Excel works with serial numbers for the date and time.

The formula in cell "B16" and "C16" is the following: The two cells have different formats. The first cell has the format of the 'General' and the other cell has the format 'Time'.

=TIMEVALUE(A16)

Converting a decimal number in the the time display

Converting decimal numbers to display the time (hours:minutes)

In this part of the tutorial, at the beginning I want to emphasize the following. There are situations when Excel using record the decimal number as the time (hours and minutes). Here we need to know to convert the decimal number of the time has a double meaning. You can see this if you look at the Excel serial_number for each time entered in Excel. Also, pay attention to record the decimal number in column 'A'.

eg. 6,5 (6.5) is a decimal number. This number can be displayed in two ways for the time (hours/minutes)

Decimal number converted to really excel time: We want to really excel Excel displays time in the relation to a decimal number. Six hours and thirty minutes = 6.5 (show excel time)

6,5 (6.5) => 6:30 => 0.270833333333333 (really excel time, see the fourth picture of this tutorial, six hours and a half)

Decimal number converted to a visual user Excel Hours: So how written dec. No, we want it to look, and time (hours/minutes). Six hours and fifty minutes = 6.5 (displaying the user time)

6,5 (6.5) => 6:50 => 0.284722222222222 (visual user time in Excel, six hours and 50 minutes)

Furthermore, in the image below you will see a few tables where the converted decimal value in the Excel time (hours/minutes). Column "A" contains entries decimal numbers or values.

The formula in the cell 'C3' is as follows: This formula converts a decimal value in the Excel time.

=IFERROR(TIME(IFERROR(LEFT(A3;FIND(",";A3)-1);A3);IF(LEN(IF(IFERROR(RIGHT(A3;LEN(A3)-FIND(",";A3));"")>0,9;IFERROR(RIGHT(A3;LEN(A3)-FIND(",";A3));"")&0;""))<2;IF(IFERROR(RIGHT(A3;LEN(A3)-FIND(",";A3));"")>0,9;IFERROR(RIGHT(A3;LEN(A3)-FIND(",";A3));"")&0;"");LEFT(IF(IFERROR(RIGHT(A3;LEN(A3)-FIND(",";A3));"")>0,9;IFERROR(RIGHT(A3;LEN(A3)-FIND(",";A3));"")&0;"");2));0);A3/24)


The formula in the cell 'E3' is as follows: This formula extract the hours from the cell 'C3' and returned as an integer.

=INT(HOUR(C3))

The formula in the cell 'F3' is as follows: This formula, combined with the cell 'A3' converted time from the 'C3' cells and as a result returns the number of minutes for the respective time. The formula converted hours in the minutes and then adds them to the remaining minutes.

=INT(HOUR(C3))*60+IF(LEN(IF(IFERROR(RIGHT(A3;LEN(A3)-FIND(",";A3));"")>0,9;IFERROR(RIGHT(A3;LEN(A3)-FIND(",";A3));"")&0;""))<2;IF(IFERROR(RIGHT(A3;LEN(A3)-FIND(",";A3));"")>0,9;IFERROR(RIGHT(A3;LEN(A3)-FIND(",";A3));"")&0;"");LEFT(IF(IFERROR(RIGHT(A3;LEN(A3)-FIND(",";A3));"")>0,9;IFERROR(RIGHT(A3;LEN(A3)-FIND(",";A3));"")&0;"");2))

The formula in the cell 'G3' is as follows:

=(INT(HOUR(C3))*60+IF(LEN(IF(IFERROR(RIGHT(A3;LEN(A3)-FIND(",";A3));"")>0,9;IFERROR(RIGHT(A3;LEN(A3)-FIND(",";A3));"")&0;""))<2;IF(IFERROR(RIGHT(A3;LEN(A3)-FIND(",";A3));"")>0,9;IFERROR(RIGHT(A3;LEN(A3)-FIND(",";A3));"")&0;"");LEFT(IF(IFERROR(RIGHT(A3;LEN(A3)-FIND(",";A3));"")>0,9;IFERROR(RIGHT(A3;LEN(A3)-FIND(",";A3));"")&0;"");2)))*60

Converting text of time into Excel time

Convert and extract decimal values entered time in Excel

In columns E, F and G you notice numeric values that are the result of converting the decimal number in the range "A:A14. The first column shows the hours, the second column shows the minutes and the third column shows the seconds.

The formula in the cell 'E11' is as follows: This formula converts a decimal number from 'A11' in the the hours as integer value

=VALUE(IFERROR(LEFT(A11;FIND(",";A11)-1);A11))

The formula in the cell 'F11' is as follows: This formula converts a decimal number from 'A11' in the minutes as a whole numerical value. The formula is related to the columns 'A and E'

=VALUE(IF(LEN(IF(IFERROR(RIGHT(A11;LEN(A11)-FIND(",";A11));"")>0,9;IFERROR(RIGHT(A11;LEN(A11)-FIND(",";A11));"")&0;""))<2;IF(IFERROR(RIGHT(A11;LEN(A11)-FIND(",";A11));"")>0,9;IFERROR(RIGHT(A11;LEN(A11)-FIND(",";A11));"")&0;"");LEFT(IF(IFERROR(RIGHT(A11;LEN(A11)-FIND(",";A11));"")>0,9;IFERROR(RIGHT(A11;LEN(A11)-FIND(",";A11));"")&0;"");2)))+E11*60

The formula in the cell 'G11' is as follows: This formula converts a decimal number from 'A11' in the second, as a whole numerical value. The formula is related to the columns 'A and E'

=VALUE(IF(LEN(IF(IFERROR(RIGHT(A11;LEN(A11)-FIND(",";A11));"")>0,9;IFERROR(RIGHT(A11;LEN(A11)-FIND(",";A11));"")&0;""))<2;IF(IFERROR(RIGHT(A11;LEN(A11)-FIND(",";A11));"")>0,9;IFERROR(RIGHT(A11;LEN(A11)-FIND(",";A11));"")&0;"");LEFT(IF(IFERROR(RIGHT(A11;LEN(A11)-FIND(",";A11));"")>0,9;IFERROR(RIGHT(A11;LEN(A11)-FIND(",";A11));"")&0;"");2)))+E11*3600

Extracting  of hours and minutes from a decimal number that is entered as time

In the picture below in the 18th row you see a decimal number as time. In columns 'E and F' were singled out in particular hours and in particular a minute.

The formula in the cell 'E19' is as follows: This formula extract the hours from decimal values in column 'A' and 'returns a whole numerical value.

=VALUE(IFERROR(LEFT(A19;FIND(",";A19)-1);A19))

The formula in the cell 'F19' is as follows: This formula extract minutes from the decimal values in column 'A' and as a result returns an integer value.

=VALUE(IF(LEN(IF(IFERROR(RIGHT(A19;LEN(A19)-FIND(",";A19));"")>0,9;IFERROR(RIGHT(A19;LEN(A19)-FIND(",";A19));"")&0;""))<2;IF(IFERROR(RIGHT(A19;LEN(A19)-FIND(",";A19));"")>0,9;IFERROR(RIGHT(A19;LEN(A19)-FIND(",";A19));"")&0;"");LEFT(IF(IFERROR(RIGHT(A19;LEN(A19)-FIND(",";A19));"")>0,9;IFERROR(RIGHT(A19;LEN(A19)-FIND(",";A19));"")&0;"");2)))