Time Formulas

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
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.

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
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.

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.

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.

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.

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.