EDATE & EOMONTH
EDATE and EOMONTH are two date functions in Excel. They both return the serial number of dates and add & subtract months from a starting date. The difference is EOMONTH “returns the serial number for the last day of the month that is the indicated number of months before or after start_date”. - Microsoft Support Office 2017
What do you mean by “serial number”?
In Excel, each date has a serial number. If you type a date in, and change the format to “General”, you’ll notice the date that you entered becomes a 5-digit number.
Excel Formula: =EDATE(start_date, months); =EOMONTH(start_date, months)
Formula Made Easy
Example - EDATE
EDATE adds and subtracts months from the start date returning any day of the month that you specified in your start date. If I used negative numbers, it will subtract (rather than add) the number of months from my start date.
Example - EOMONTH
I want to have a record of every month in 2017. In the first cell, cell A1, I’m going to put 31/01/2017 (start date). Since I want a record of every month, I’m going to add one month from the start date.
If I were to put the above information into the Excel formula, (into cell B1) it will look like: =EOMONTH(A1,1) producing a result, 28/02/2017.
If your result shows up in the serial number (5-digit) format, simply change the format to “Date” or “Short Date”.
What happens if I put -1 instead of 1; meaning the formula will be =EOMONTH(A1,-1)? The result will be 31/12/2016, going back one month.
Note:
"Best before" image is from shutterstock.com
댓글