Excel Date Functions

Excel Date Functions allow you to calculate other dates based on a given date. For example, we often want to see data for the current period and prior period, or same period last year, or even see a full period of data based on a single date entered. This is quite easy to calculate using Excel's Date functionality.

There are 4 easy to use Date related functions:

=DATE() - returns the serial number of a particular date. So, =DATE("2008","5","31") means May 31st, 2008.

The below functions can be used in combination with the =DATE() function to obtain the specific year, month or day of a whole date.

=YEAR() - converts a serial number to a year. So, =YEAR("31/05/2008") returns 2008. And =YEAR("31/05/2008")-1 returns 2007 because we subtracted 1 from the year returned from the year function.

The rest work the same way.

I have attached a quick cheat sheet to help figure this out. You can get any date you need, including leap years, end of a month, previous periods and more.

Remember this is simply standard Excel functionality that works the same across all versions of Excel I have ever used. Should you need additional information, Excel's online help is a great resource. Simply type in "Date" in the help search window.

1 comment

Please sign in to leave a comment.