# Basic Of Time in Excel

In this **Excel Example Tutorial** I will try to show action in Excel, related to the **calculation of the Time, hours and seconds**. This Excel 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 **Excel 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.

Pay attention, you may need as a separator of arguments formula use a **comma (,) instead of a semicolon (;)**.

Copy formula to Notepad and with Find/Replace change semicolon to comma.

## Displaying the Time or Hours as Time Format in Excel

All users of Excel who work with the **24 hours time** (No 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 "**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 24.

You Notice in the picture below that "one day" has **24 Hours** (column K) as well as other Values/SerialNumbers in Minutes and Seconds.

NOTE! Separator for Decimal Number in Croatia is a Comma

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 Number

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

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

## 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 decimal numbers to display the time (hours:minutes)

In this part of the Excel 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' (blue font) 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 f**ormula 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 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)))