In Excel, you can use formulas for times as well. Although it may not be extremely common for all Excel users, it is useful to understand how to work with times in your worksheet cells. Check out the following articles to explore the various uses for formulas regarding calculating and recording time values in Excel.

Tips, Tricks, and Answers

The following articles are available for the 'Time Formulas' topic. Click the article''s title (shown in bold) to see the associated article.

   Adjusting Times for Time Zones
Collect a series of times in a worksheet, and you might need to adjust those times for various time zones. This involves a simple math operation, as described in this tip.

   Automatically Converting to GMT
You know what time it is, right? (Quick; look at your watch!) What if you want to know what time it is in Greenwich, England? Now you need to know how to convert times from your locality to GMT. This tip shows you how.

   Automatically Entering a Data Entry Time
Excel worksheets can be used to keep track of all sorts of information. You may want to use it, for instance, to track entries made at specific times. Here are ways to "time stamp" your entries automatically.

   Calculating an Average Time
When working with elapsed times, you may want to calculate an average of those times. This tip demonstrates just how easy it is to get the average you seek.

   Calculating Elapsed Time with Excluded Periods
When using Excel to calculate elapsed time, there can be all sorts of criteria that affect the formulas you would otherwise use. This tip examines a way that you can exclude certain regular periods of time in calculating your elapsed time periods.

   Calculating TV Time
In some industries it is necessary to work with time resolutions of less than a second. If you need to keep track of such detailed time divisions, you'll appreciate the techniques presented in this tip.

   Checking for Data Entry Errors for Times
When you enter a time value into Excel, the program tries its hardest to make the value into a valid time. This can lead to some erroneous results, as described in this tip.

   Checking for Time Input
Need to know if a cell contains a time value? Excel doesn't contain an intrinsic worksheet function to answer the question, but there are some easy ways to approach the problem.

   Combining and Formatting Times
Excel allows you to store times in your worksheets. If you have your times stored in one column and an AM/PM indicator in another column, you may at some point need to combine these into a single column. Here's how.

   Converting Numeric Values to Times
If you have a bunch of times entered into cells without the colon between the hours and minutes, chances are good that Excel interpreted the time as a numeric value. If you later want to convert those values into real times that Excel can understand, you'll appreciate this tip.

   Converting UNIX Date/Time Stamps
If you import information generated on a UNIX system, you may need to figure out how to change the date/time stamps to something that Excel can recognize and work with. The conversion is easy, once you understand the way in which the date/time stamps are figured.

   Converting UTC Times to Local Times
Dates and times are often standardized on UTC time, which is analogous to GMT times. How to convert such times to your local time is a relatively simple process, once you know how your local time zone relates to GMT.

   Counting Times within a Range
Excel allows you to easily store dates and times in your worksheets. If you have a range of cells that contain times and you want to figure out how many of those times are between some starting and stopping point, then you'll appreciate the techniques in this tip.

   Dealing with Large Numbers of Seconds
When adding values to a time to calculate a new time, you may naturally choose to use the TIME function. This can cause some unforeseen problems, however, as are addressed in this tip.

   Dealing with Midnight Ending a Day
Dealing with times in Excel is fairly straightforward, except when it comes to midnight. Some people prefer that midnight ends a day and others prefer that it starts the day. Here's a discussion as to why Excel uses an approach that favors the "day starters."

   Dealing with Small Time Values
It is no secret that you can store time values in an Excel worksheet. But do you really know how small of a time value you can store and display? Here's a discussion that addresses that very topic.

   Determining If a Date and Time is within Working Hours
Excel is great at working with times and dates. Sometimes, though, it can be a bit tricky to figure out how to work with both times and dates at the same time. This tip examines one situation in which checking both must occur.

   Displaying a Result as Minutes and Seconds
When you use a formula to come up with a result that you want displayed as a time, it can be tricky figuring out how to get the display you need. This tip explains how you can convert your formula's result to get just what you want.

   Displaying Elapsed Time in Decimal Format
Want to display an elapsed time as a decimal format? It's pretty easy to do, if you know that a very simple formula is required.

   Entering Large Time Values
If you need to input humongous times into a worksheet, you may run into a problem if you need to enter times greater than 10,000 hours. This tip explains the full problem and provides some ideas on getting around the problem.

   Entering Negative Times
Do you need to enter negative times into a worksheet? Excel doesn't really provide a way to do that but understanding why (and what you can do about it) is essential.

   Entering or Importing Times without Colons
Enter a time into a cell and you normally include a colon between the hours and minutes. If you want to skip that pesky colon, you'll need to use one of the techniques described in this tip.

   Rounding Time
Need to round the time in a cell to a certain value? There are a couple of ways you can do this with a formula.

   Rounding to a Half Hour and Formatting the Display
Excel is quite flexible in how it allows you to format and display times. However, some formatting and displaying can take considerably more work, as demonstrated in this tip.

   Shortcut to Enter GMT
Entering the current time into a cell is easy, as Excel provides a built-in shortcut to accomplish the task. Here's a one-line macro you can use to enter an adjusted time into any cell you want.

   Taking the Time into Account in a Formula
Need to check the current time in a formula you are putting together? It can sometimes be tricky to remember what Excel considers as the time and how you should work with it. Here is some guidance on the topic.

   Using Excel for Timing
Excel allows you to store times in a worksheet. If you want to use Excel to time certain events, there are a couple of ways you can approach the task.

   Working with Elapsed Time
Work with times in a worksheet and you will eventually want to start working with elapsed times. Here's an explanation of what they are and how to best work with them in Excel.