Dates and Times

Tips, Tricks, and Answers

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

Adjusting Date Values by Keypress
Excel allows you to store dates in a cell. Wouldn't it be great if you could select a cell containing a date and then increment or decrement the date by pressing a key? Here's how you can accomplish the task relatively easily.

Backwards Date Parsing
Enter information into a worksheet, and you come to anticipate (and count on) how Excel will interpret that information and store it in the worksheet. If you suddenly find that Excel isn't parsing your date entries correctly, there are two possibilities you should check out right away.

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.

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.

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

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.

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.

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.

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.

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.

How Excel Stores Dates and Times
Excel stores dates and times internally using what is called a serial number. This tip explains how that serial number is derived.

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.

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.

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.

Macro for Month Name
Need to know how to generate a full month name based on a date? It's easy to do, as discussed in this tip.

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.

Sorting Dates by Month, Take Two
Another way to sort dates by month in a particular column.

Tombstone Date Math
Doing math with dates is easy in Excel. Doing math with old dates—such as those you routinely encounter in genealogy—is a different story. Here's how you can figure out birth or death dates given one or the other and a precise age.