Subscriber only lesson.

Sign up to the Microsoft Excel - Basic & Advanced course to view this lesson.

## 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:

2013, **2016**.

## Exercise files

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

Temporal Functions.xlsx13.5 KB Temporal Functions - Completed.xlsx

13.5 KB

## Quick reference

### Topic

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.