**Time is a slippery beast because it can be clock time or duration. **

**This post shows you how to create formulas that use time functions, how to convert from decimal time to clock time, how to create custom time formats using special placeholders and how to mix all three.**

**I've also included a couple of pro techniques to help you overcome some prickly problems that sometimes arise.**

## 1. Working with time functions in Excel

Time can be used in different contexts, such as an **actual time** (e.g. 9:30am) or a **duration** (e.g. 3 hours and 40 minutes).

In Excel, many problems arise because time is not based on a decimal system. There are 60 seconds in a minute (not 100), 60 minutes in an hour (not 100) and 24 hours in a day (not 100).

Therefore, care must be taken when creating calculations involving time.

Dates are stored as whole numbers in Excel.

Day 1 = 1 January 1900.

Day 2 = 2 January 1900

Day 3 = 3 January 1900 etc.

Any digits after the decimal point represent time.

Excel uses a concept called **modulo 24**.

For example, **10:45 am** on **11 April 2014** is stored as **41,740.4479207176**.

## 2. Inserting the current time in Excel

There are two ways to insert the time.

The first way is to insert a **static** time by pressing **Ctrl + Shift + ;** (semicolon). 'Static' means that it doesnâ€™t automatically update, so if you insert the time now, then in ten minutes time the time shown in the cell will not have changed.

The second way is to insert a **dynamic** time by typing **=NOW()**, 'Dynamic' means that the time shown in the cell is always updating, so in 10 minutes time, when the spreadsheet is refreshed, the correct time will be displayed.

To update the spreadsheet data at any time, press **F9**.

## 3. Common time functions

### a) TIME function

**Description**: Construct a time, given the hours, minutes and seconds, e.g. construct a time of 9:30pm.

###### =TIME(hour, minute, second)

### b) HOUR function

**Description**: Extracts the hour component of a time, e.g. given 9:30pm, the hour component is 21.

###### =HOUR(A1) where A1 contains the time

### c) MINUTE function

**Description**: Extracts the minute component of a time, e.g. given 9:30pm, the minute component is 30.

###### =MINUTE(A1) where A1 contains the time

### d) SECOND function

**Description**: Extracts the second component of a time, e.g. given 9:30:05pm, the second component is 05.

###### =SECOND(A1) where A1 contains the time

## 4. Choosing a standard time format

The time can be formatted in one of several ways using the **Format Cells** dialog box.

1. Press **Ctrl 1**** **or click the launcher in the bottom-right corner of the **Number** group of the **Home** ribbon.

2. Choose the **Number** tab, if necessary.

3. Select **Time** from the **Category** list.

4. Choose a time format from the **Type** list.

## 5. Creating a custom time format

You can also create a **custom time format** to your exact specifications.

1. Press **Ctrl 1**** **or click the launcher in the bottom-right corner of the **Number** group of the **Home** ribbon.

2. Choose the **Number** tab, if necessary.

3. Select **Custom** from the **Category** list.

4. Clear the contents from the **Type** box,

5. Create a custom time format using the following placeholders:

## 6. Calculating durations beyond 24 hours

If two times are added, Excel wraps any result beyond 24 hours to zero in the same way as it wraps dates to the next month (at the end of a month) and years to the next year (past 31 December).

If this is not required or wanted, reformat the cell to: [h]:mm.

For example, when adding 7 hours to 21 hours (e.g.a timesheet). the required result is 28 hours.

The **hh:mm** format returns **04:00** (i.e. 4 hours beyond 24)

The **[h]:mm** format returns **28:00** as required.

## 7. How to handle negative time

Negative times are not permitted. Excel will fill the cell with '#'.

If you are trying to calculate the difference between 2 times, then use the ABS function which converts any negative into a positive.

###### =ABS (time2 - time1)

If two times are being compared and one falls one side of midnight (e.g. Thursday 9pm) and the second falls the other side of midnight (e.g. Friday 3am) then the formula needs to manipulate the times so that they fall on the same day and can be easily compared.

The following formula adds one day to the first time, if it falls on a different day to the second time.

###### =time1 + (time2 < time1)

This part of the formula inside the brackets uses the true/false concept where TRUE = 1 and FALSE = 0.

Remember that all the digits to the left of the decimal point represent the DAY NUMBER, so adding 1 means the next day.

## 8. Converting decimal time to clock time

There are **24** hours in a day, **1440** minutes in a day and **86400** seconds in a day.

To convert **decimal** hours, minutes or seconds to a time, divide the value by the appropriate unit, then format the result cell to a time format.

To convert **2.75 hours** (decimal) to hours and minutes, this formula results in **2:45** (when formatted as time):

###### = 2.75 / 24

To convert **150 minutes** (decimal) to minutes (time format) this formula results in **2:30** (when formatted as time):

###### = 150 / 24 / 60

or

= 150 / 1440

To convert **200 seconds** (decimal) into minutes and seconds, this formula results in **00:03:20** (when formatted as time)

###### = 200 / 24 / 60 / 60

or

= 200 / 86400

## 9. Key Takeaways

I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.

Have a fantastic day.

About the author

Jason Morrell

Jason Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.

He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.

Purely for amusement he sometimes talks about himself in the third person.

SHARE