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.
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 Time Differences between Two Machines
Want to know how much of a time difference there is between your machine and a different machine? This tip provides some suggestions on how you can find an answer.
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 Time Notation to Decimal Notation
Want to convert an elapsed time, such as 8:37, to a decimal time, such as 8.62? If you know how Excel stores times internally, coming up with the conversion formula is a snap.
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.
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 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.
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.
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.
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 the Nearest Quarter Hour
When entering times in a worksheet, you may have a need to round whatever you enter to the nearest 15-minute increment. There are a variety of ways you can approach this problem, as you learn in this tip.