Topic: Time Formulas

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

Adjusting Times for Time Zones Most people, when they collect data that includes times, collect it from their local office or vicinity. If you collect data from a location in a different time zone, you may want to adjust that data so that it fits your time zone. This tip explains how you can make the adjustment easily. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Calculating Time Differences between Two Machines If you need to compare the time on your machine with the time maintained by a server on your network, you’ll be interested in the information presented in this tip. The comparison can be done, but not by any native functions available in Excel or VBA. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Checking for Time Input Do you want to know whether the information entered in a cell is a time value? Determining the status of such data is not as clear-cut as one might hope. This tip examines some ways you might be able to determine if a time value was really entered into a cell. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Converting Numeric Values to Times If you enter times such as “14:45” into a worksheet without the colon, Excel interprets them as regular numbers. Getting Excel to recognize these numbers as real times involves the use of formulas or macros. This tip examines the many different ways you can get the desired results. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Entering or Importing Times without Colons You can speed up the entry of times, provided you can figure a way to not need to enter the colon between hours and minutes. (It is much faster to enter 1124 rather than 11:24.) This tip explains a couple of ways that you can add the colon after the fact, as well as how you can use a macro to insert the colon automatically. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Rounding Time Do you need to round time values to the nearest quarter hour or some other portion of an hour? You can use the MROUND function (part of the Analysis ToolPak) to accomplish the rounding, as demonstrated in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Tips, Tricks, and Answers

Perplexed by Pivots?

Helpful Links

Great Info!

RSS Feeds