Time Functions, Time Formats and Time Calculations: 8 Simple Ways to Work Smarter With Time in Excel

AUTHOR:  JASON MORRELL 

Jason Morrell

Reading Time:
  minutes remaining

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.

  • The hour component of a time is stored as 1/24.
  • The minute component of a time stored as 1/24/60 or 1/1440. There are 1,440 minutes in a day.
  • The second component of a time is stored a 1/24/60/60 or 1/86400). There are 86,400 seconds in a day.

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

  • 11 April is 41,740 days after 1 January 1900.
  • .4479207176 is the sum of 10/24 (10 hours), 45/1440 (45 minutes) plus a little bit for the seconds and milliseconds

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.

Choosing a time format

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:

  • h   - display a one digit hour (2 digit hours will still display as 2 digits)
  •  hh   - always display a 2 digit hour (2 will display as 02)
  • [h] - used for duration to display the total actual hours (e.g. 42 hours worked). Without the square brackets, the hour figure resets to zero beyond 24 hours.
  •  m   - display a one digit minute (used for duration, not clock time)
  • mm - display a 2 digit minute (standard convention for clock time)
  • s   - display a one digit second
  • ss  - display a 2 digit second
  • am/pm  - display clock time in 12-hour format (8:30pm vs 20:30)

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.

  • If (time2 <time1) is true then 1 is added to time1.
  • If (time2 < time1) is false then 0 is added to time1.

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

  • Press Ctrl + Shift + ; to insert the current time (the information is static, i..e. it won't update)
  • Use =NOW() to generate the current time. This will be updated when the workbook is opened or the when you press F9.
  • The HOUR, MINUTE and SECOND functions extract each component from a given time.
  • The TIME function creates a time, given the hour, minute and second data.
  • You can create a custom time format by using the following placeholders: h, mm, ss, [h], am/pm.
  • The H placeholder will only show hours up to 24 and then wrap back to zero. To show larger hour numbers (e.g. 40 hours worked),use the [H] placeholder.
  • You can use the following punctuation placeholders: colon, period, dash, parentheses.
  • You can prefix a custom format with a base colour within square brackets, e.g. [red].
  • To convert a number of hours (e.g. 2.75) into hours and minutes, divide the value by 24 and format as hours and minutes to show 2:45.
  • To convert a number of minutes (e.g. 165) into hours and minutes, divide the value by 1440 and format as hours and minutes to show 2:45.
  • To convert a number of seconds (e.g. 200) into hours, minutes and seconds, divide the value by 86400 and format as hours, minutes and seconds to show 00:03:20.

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your day!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

Office Legends Academy | Making Office Legends

Master Microsoft Office
in 30 days or less

more from

Office Mastery logo
Read More
SOS-Click: The Easy Automated Backup Tool For Home Users
Read More
6 Excel Templates to Help Organize Your Daily Activities
Read More
10 Ways Excel Consultants Can Help You Fully Leverage Your Business Spreadsheets
Read More
How to fix formulas: 21 Fatal Formula Fails (and how to avoid them)

JOIN THE DISCUSSION

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>