Date and Time Functions

Tips, Tricks, and Answers

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

Automatically Advancing by a Month
Excel allows you to perform quite a few operations using dates in your worksheet. Sometimes, however, the answer may not be immediately obvious. For instance, if you want to perform an operation that always advances the date to the beginning of the next month once half the month has passed.

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.

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.

Counting Dates in a Range
Excel makes working with a list of dates relatively easy. If you have a list of dates, you may need to know how many of those dates fall within a certain range. Here's how to get the count you need.

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.

End-of-Month Calculations
Don't want to use the EOMONTH function to figure out the end of a given month? Here are some other ideas for discovering the date you need.

EOMONTH Function is Flakey
Some users have reported problems using the EOMONTH function in Excel 2007. The problem seems to be related to Excel's compatibility mode and the fact that the function was moved from the Analysis ToolPak to Excel itself.

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.

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.

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.

Using Excel for Timing
Excel allows you to store times in a worksheet. If you want to use Excel to time certain events, there are a couple of ways you can approach the task.

Using the EOMONTH Function
If you need to determine the date of the last day in a month, it's hard to beat the flexibility of the EOMONTH function. This tip explains how to use this function in your formulas.

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.

Working with Elapsed Time
Work with times in a worksheet and you will eventually want to start working with elapsed times. Here's an explanation of what they are and how to best work with them in Excel.

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.