## About this lesson

Learn to work with the EOMONTH and EDATE functions that easily shift dates from one month or year to another.

## Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

## Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Temporal Functions.xlsx24.3 KB Temporal Functions - Completed.xlsx

24.4 KB

## Quick reference

### Temporal Functions

Working with the EOMONTH and EDATE functions that shift dates easily from one month or year to another.

### When to use

The functions are extremely useful and easy to use when you need to return either a date from a different month or year, or when you need to determine the end of a given month.

### Instructions

EOMONTH

- Returns the end of the specified month

=EOMONTH(A1,-1) |
Returns the end of the month previous to the date in A1 |

=EOMONTH(A1,0) |
Returns the end of the month for the date in A1 |

=EOMONTH(A1,1) |
Returns the end of the month after the date in A1 |

- The negative and positive month offsets can be as high as you wish (12 is a full year, 24 is 2 years, etc.)

EDATE

- Returns the same day in the specified month
- Is Feb 28/Feb 29 aware

=EDATE(A1,-1) |
Returns the same day for the month previous to the date in A1 |

=EDATE(A1,0) |
Returns the date in A1 (the day offset by 0 months) |

=EDATE(A1,1) |
Returns the same day for the month after the date in A1 |

- The negative and positive month offsets can be as high as you wish (12 is a full year, 24 is 2 years, etc.)

Compatibility

- The EOMONTH and EDATE functions can be used immediately in Excel 2007 or higher
- Even though the Analysis Toolpack (which holds these functions) existed and was installed with Excel since at least Excel 97, it was not activated by default

Activating the Functions in Excel 2003 and earlier

- The user must go to Tools, Addins, check the box next to Analysis Toolpack, and click OK
- Once done, that user will not have to make this change again, as it persists on the user’s profile

Lesson notes are only available for subscribers.