Topic: Dates and Times

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

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

Calculating TV Time Excel does a great job handling times, automatically recognizing them and putting them into a format that you can use in various calculations. This is not the case with specialized times, such as those used in the TV industry, where frames must be taken into account. This tip describes different ways that you can work with TV times in Excel. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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 an Unsupported Date Format If you import data produced by a program other than Excel, it might include dates stored in a format not automatically recognized by Excel. This tip shows how you can do the necessary data conversion so that Excel can recognize the dates. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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

Determining the Day of the Month You can use the Day function to find the day of the month within a macro. This tip explains how. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Displaying Negative Times Excel allows you to perform math using times as operands. If you subtract a later time from an earlier time, you should end up with a negative time. If you don’t see the negative time, it could be because of the way you have Excel configured. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Entering Dates Without Separators When doing data entry into a worksheet, you might want to enter dates without the need to type the separators that are normally part of those dates. Here’s a discussion on how this can be done in Excel, along with the benefits and drawbacks of using such an approach. 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

Inserting the Current Time with Seconds If you need to insert the current time, with seconds, then you’ll need the macro discussed in this tip. It’s easy to use and can give you just the information you need. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Is Daylight Savings Time in Effect? Daylight savings time is supported by modern computers, but getting the information into Excel as to whether daylight savings time is in effect can be a challenge. This tip discusses two ways that you can get the desired information. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Leap Years and Fiscal Periods If your fiscal period ends in February, and you need to calculate the date either a year in the past or the future, then you will run into the problem of how to deal with leap years. This tip explains some of the formulas and functions you can use to achieve the desired calculation. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Macro for Month Name Do you need to frequently enter the current month’s name into a cell? Excel tracks the current date, but there is no function that gives just the current month. The trick, then, is to find a way to extract just the month from the date, and then return the name of that month. This tip provides several different ways you can accomplish that task. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Pulling All Fridays If your work depends on knowing the dates for all the Fridays in a month or year, then you need a formulaic method of determining those dates. This tip presents several different ways you can get the desired information, including a few macros. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Sorting Dates by Month, Take Two Another way to sort dates by month in a particular column. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Tips, Tricks, and Answers

Problems with Dates?

Helpful Links

Great Info!

RSS Feeds