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

   Calculating TV Time
In some industries it is necessary to work with time resolutions of less than a second. If you need to keep track of such detailed time divisions, you'll appreciate the techniques presented in this tip.

   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.

   Converting an Unsupported Date Format
Excel makes it easy to import information created in other programs. Converting the imported data into something you can use can present a challenge. Here's how to make a change from an unsupported date format to one that Excel can work with.

   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.

   Date for Next Wednesday
When working with dates, it is often helpful to be able to calculate some date in the future based on a starting date. Figuring out future days of the week based on some starting date can be trickier still. Here's a wide range of formulas that can help you figure out the date of the next Wednesday, starting from any date.

   Determining the Day of the Month
Want to figure out the day of the month represented by a particular date? You can use the Day function in VBA to get the value you need.

   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.

   Finding the Previous Work Day
Excel has a number of functions that are available as an add-on in the Analysis ToolPak. One of these functions allows you to return the date of a previous workday. This tip discusses using this tool.

   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.

   Leap Years and Fiscal Periods
Need to figure out when a fiscal year ends when that period does not correspond to the calendar year? Here are some ways that you can do the calculation and make sure you take leap years into account.

   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
It can be handy to know when specific weekdays occur within a range of dates. Figuring out this information, using formulas, can be tricky. Here's how to get just the dates you need.

   Setting a Default Date Format
Enter a date into a cell, and Excel allows you to format that date in a variety of ways. Don't see the date format you want? You may have a wider number of formats available than you think you do.

   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.

   Using Early Dates
Excel is brilliant at handling datesâ€"as long as they aren't dates earlier than the base date used by the program. If you need to use earlier dates, then you'll want to be aware of the information in this tip.