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.
=MONTH()
=DAY()
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.
Date
Votes
1 comment
-
Jet Reports Historic Posts Yes, this function in combination with the "integer" table (NAV) and a NL function can give you a nice tool to generate columns/rows simulating a time dimension (works great on Ledger Entry tables).
Please sign in to leave a comment.