Excel Tip of the Week #249 - Revisiting making dates with formulas

Hello all and welcome back to the Excel Tip of the Week!  This week, we have a Creator level post in which we're looking back at how to use the DATE, EDATE, and EOMONTH functions to construct date-format data.  This was originally covered back in TOTW #60.

DATE

The first function we might use is the simple DATE function.  This converts a separate day, month, and year input into a single Excel date value.  As a refresher, Excel stores dates in memory as numbers (count of days since 1st January 1900).  It can convert something like 07/08/2018 into a date, but anything not in that format won't be understood as a date and filtering / formulas using it won't be understood.  So the DATE function is a great way to convert data in other formats into a date that Excel can understand and work with.

The syntax is pretty straightforward:

=DATE(year, month, day)

Note that the month and day variables are smart - so for example if you have a month input of 13, that will give you the January of the following year.  Likewise the day argument can overflow the number of days in the given month.

This means that you can make a really simple function that will count through all the days in a year with e.g.:

=DATE(2018,1,ROW())

Copy this down and you're set!

EDATE

It's very simple to compute a date, say, 10 days on from a given starting date - you can just add 10 to the date cell to get the desired result.  Moving forward a given number of months is trickier, as they're all different lengths; EDATE is the function you need in these situations.

Here's the syntax:

=EDATE(start date, months)

Remember that (as discussed above), the "start date" can't be typed directly, but needs to either be a reference to a cell with a date value in it, or a DATE function.  The number of months can be positive or negative depending on whether you want to count forwards or backwards.

There isn't any special way of moving whole years other than inputting the appropriate multiple of 12 into the 'months' input.

EDATE is also a little smart; if the starting date is on a day that is beyond the length of the target month (e.g. counting forward 1 month from 31st January), you will get out the month-end date instead (so 28th February in this example).

EOMONTH

EOMONTH, short for "end of month", does pretty much what it says on the tin - it identifies the appropriate month-end date for a given input date.

=EOMONTH(start date, months)

The syntax is identical to EDATE because EOMONTH can also be used to move the input date forward or backward.  Most of the time however you would use 0 here.  Whatever the input date is, EOMONTH will generate a date value for the last day in the same month and year.

All these functions are worth knowing about, even if you don't use any of them that often - because the things they do are really tricky to do any other way.  Even if you don't immediately remember how to write them or even their names, remembering that these kinds of tasks are possible in Excel will prompt you to look them up and save you time in the long run!

Previous post - VBA case study: Ordinals (1st, 2nd, 3rd)
TOTW index 
Next post - Goal Seek redux

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.

Anonymous