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