Topic: Date and Time Functions

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

Automatically Advancing by a Month Do you need to “jump” the date in a cell forward by a month based on some trigger value? This tip explains several ways, including formulas and macros, that you can use to accomplish the task. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Calculating Business Days Excel allows you to determine the number of work days (business days) between two dates by using the NETWORKDAYS worksheet function. This tip explains different ways you can immediately put the function to work. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Calculating Week-Ending Dates When working with dates, you may need to figure out all the dates on which weeks end in a given year. There are several formulaic ways to accomplish this task, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Counting Dates in a Range Given a table of dates in a worksheet, you may want to determine how many of those dates are within a certain date range. This tip explains the formulas you can use to determine the totals you need. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Elapsed Days as Years, Months and Days Excel allows you to work with dates and times, but sometimes it takes some real ingenuity to get your output in just the format you want. This tip explains how to display an elapsed time in years, months, and days. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

End-of-Month Calculations Need to put the last day of the month in a range of cells? This tip explains a few different ways you can quickly add the desired information to your worksheet. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

How Excel Stores Dates and Times Excel is outstanding at working with numbers. In fact, it stores both dates and times internally as numbers. This tip explains how that storage is done. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Parsing Non-Standard Date Formats If you import information into a worksheet from a different program, you may be surprised when Excel doesn’t parse the dates correctly. This tip describes some ways in which you can help Excel to make sense of the imported data. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

The EDATE Function You can use the EDATE function to determine a date a number of months in the past or future. This function is part of the Analysis ToolPak. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Unique Date Displays Sometimes you need to do some math with date values, and then display the result in a unique manner. This can be done using a custom format, with some caveats, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Using Excel for Timing Excel can be used to store starting and ending times for any number of events. This tip provides a couple of macros that can be helpful in your time-keeping efforts. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Using the EOMONTH Function The EOMONTH function returns the last day of a month you specify, either in the future or in the past. The function is easy to use and can be very helpful when working with dates. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Weekdays in a Month Excel has no intrinsic formulas that allow you to determine how many of a particular weekday occur within a month. You can create such a formula, however, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Working with Elapsed Time Since Excel stores times as numeric values, you can perform arithmetic operations on those values. This allows you to work with elapsed time, but doing so can present some results that may be confusing. This tip explains the ins and outs of working with elapsed time in Excel. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Working with Minutes For many Excel users—particularly beginners—working with elapsed time can be bewildering. This tip explains how you can display and work with elapsed minutes in a workbook. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Tips, Tricks, and Answers

Macros 'Power Up' Excel!

Helpful Links

Great Info!

RSS Feeds