## 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 - "

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.

**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

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.## 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.## 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 "

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.

**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.

Columns 'D/I' in the picture above you see the

The formula in cell 'D2' is next:

=A2*60*24

**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

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

**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).

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

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

=SUM(C2:C5)

Furthermore, in the cell 'A11' is entered time 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)

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 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

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)

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)

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)))

**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**## 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

=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)))

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)))

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

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

=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)))

**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)))