Purpose
Return value
Syntax
=DAYS360(start_date,end_date,[method])
- start_date - The start date.
- end_date - The end date.
- method - [optional] Day count method. FALSE (default) = US method, TRUE = European method.
Using the DAYS360 function
The DAYS360 function returns the number of days between two dates, based on a year where all months have 30 days. Both dates must be valid Excel dates or text values that can be parsed as dates. The DAYS360 function only works with whole numbers, time values are ignored.
Method
DAYS360 takes an optional argument called method that can be set to either TRUE or FALSE. When method is FALSE (default) DAYS360 uses a US method to compute days. When the start date is the last day of the month, it is treated like the 30th day of that month. When the end date is the last day of the month, and the start date is less than 30, the end date is treated as the 1st of the next month, otherwise the end date is treated like the 30th of the same month.
If method is set to TRUE, DAYS360 uses a European method to calculate days. In this scheme, start and end dates equal to the 31st of a month are set to the 30th of the same month.
Examples
In the formula below, DAYS360 returns 360 days with a start date of January 1, 2021 and an end date of December 31, 2021.
=DAYS360("1-Jan-2021","31-Dec-2021") // returns 360
The result of 360 is based on 12 months * 30 days in each month.
Note: In general, storing and parsing text values that represent dates is bad form and should be avoided, because it can introduce errors and parsing problems. Working with native Excel dates is a better approach.
With a start date of July 1, 2021 in A1, and an end date of December 31, 2021 in B1, the formula below returns 180:
=DAYS360(A1,B1) // returns 180
To create a date from scratch in a formula, use the DATE function . The formula below returns 90:
=DAYS360(DATE(2021,1,1),DATE(2021,4,1)) // returns 90
Notes
- The DAYS360 function only works with whole numbers and ignores time.
- If dates are not recognized, DAYS360 returns the #VALUE! error.
- If dates are out of range, DAYS360 returns the #NUM! error.
Purpose
Return value
Syntax
=EDATE(start_date,months)
- start_date - Start date as a valid Excel date.
- months - Number of months before or after start_date.
Using the EDATE function
The EDATE function returns a date on the same day of the month, n months before or after a start date. You can use EDATE to generate expiration dates, contract dates, due dates, anniversary dates, retirement dates, and other dates that derive from a start date. EDATE returns a serial number corresponding to a date . To display the result as a date, apply a number format of your choice .
The EDATE function takes two arguments , start_date and months :
- start_date - a valid Excel date to use as the starting point.
- months - a whole number that specifies how many months to move. Use a positive number of months to get a date in the future and a negative number for a date in the past.
Note: The EDATE function returns the same day of the month. If you want to get the last day of a month, use the EOMONTH function .
The EDATE function explained
With a given start date, EDATE returns a new date by adding the number of months provided. To illustrate how EDATE works, assume we want to create dates for the first day of each quarter, starting from January 1, 2024. We can do this with the following formulas:
=EDATE("1-Jan-2024",0) // returns 1-Jan-2024
=EDATE("1-Jan-2024",3) // returns 1-Apr-2024
=EDATE("1-Jan-2024",6) // returns 1-Jul-2024
=EDATE("1-Jan-2024",9) // returns 1-Oct-2024
The first formula does not change the date since months is zero. The second formula adds 3 months, the third formula adds 6 months, and the fourth formula adds 9 months to the date. In all cases, EDATE returns the 1st of the month since the start date is also the 1st. Of course, in most real-life scenarios, you will not hardcode dates into formulas like this. You will instead use cell references. If we enter the date January 1, 2024, in cell A1, the same formulas look like this:
=EDATE(A1,0) // returns 1-Jan-2024
=EDATE(A1,3) // returns 1-Apr-2024
=EDATE(A1,6) // returns 1-Jul-2024
=EDATE(A1,9) // returns 1-Oct-2024
The results are the same. And if the date in A1 is changed, EDATE will generate new dates. You can use negative numbers for months to create dates before the start date. With the same date in A1, the formulas below return dates that are 3, 6, 9, and 12 months before January 1, 2024:
=EDATE(A1,-3) // returns 1-Oct-2023
=EDATE(A1,-6) // returns 1-Jul-2023
=EDATE(A1,-9) // returns 1-Apr-2023
=EDATE(A1,-12) // returns 1-Jan-2023
Example - Basic usage
If A1 contains the date February 1, 2018, you can use EDATE like this:
=EDATE(A1,1) // returns March 1, 2018
=EDATE(A1,3) // returns May 1, 2018
=EDATE(A1,-1) // returns January 1, 2018
=EDATE(A1,-2) // returns December 1, 2017
Example - 6 months from today
To use EDATE with today’s date, you can combine it with the TODAY function . For example, to create a date exactly 6 months from today, you can use:
=EDATE(TODAY(),6) // 6 months from today
Example - Move by years
To use the EDATE function to move by years, multiply by 12. For example, to move a date forward 2 years, you can use either of these formulas:
=EDATE(A1,24) // forward 2 years
=EDATE(A1,2*12) // forward 2 years
The second form is handy when you already have a value for years in another cell and want to convert it to months inside EDATE.
Example - Sum by month
The EDATE function can be combined with the SUMIFS function to create a formula to sum values by month. This approach is seen in the worksheet below, where EDATE appears in the last argument. The idea is to sum amounts that fall between the first day of the month and the last day of the month. The formula in cell F5 is:
=SUMIFS(amount,date,">="&E5,date,"<"&EDATE(E5,1))
As the formula is copied down it creates a subtotal for each month listed in column E. You can use this same approach to count by month with COUNTIFS and average by month with AVERAGEIFS. For a detailed explanation and to download the workbook, see this page .
Example - Sequence of months
In Excel 2021 and later, you can use the SEQUENCE function with EDATE to generate a list of sequential months. In the worksheet below, the start date is in cell B5. The formula in D5 creates a list of the next 12 months, including the start date:
If the date in cell B5 is changed, a new list of dates will be generated. For a more detailed explanation, see Sequence of months .
Example - EDATE with time
The EDATE function will strip times from dates that include time (sometimes called a “datetime”). This happens because EDATE only works with whole numbers. To preserve the time in a date, you can use a formula like this:
=EDATE(A1,n)+MOD(A1,1)
Here, the MOD function is used to extract the time from the date in A1, which is then added back to the result from EDATE.
End-of-month dates
EDATE is clever about “end of month” dates when the day is 31. Starting with January 31, 2019, notice that EDATE will keep the last day of the month:
=EDATE("31-Jan-2019",1) // returns 28-Feb-2019
=EDATE("31-Jan-2019",2) // returns 31-Mar-2019
=EDATE("31-Jan-2019",3) // returns 30-Apr-2019
=EDATE("31-Jan-2019",4) // returns 31-May-2019
=EDATE("31-Jan-2019",5) // returns 30-Jun-2019
EDATE will also respect leap years:
=EDATE("31-Jan-2020",1) // returns 29-Feb-2020
However, EDATE will not maintain an end-of-month day when the day is less than 31. For example:
=EDATE("28-Feb-2019",1) // returns 28-Mar-2019
If you need an end-of-month date, switch to the EOMONTH function .
See below for more examples of formulas that use the EDATE function.
Notes
- EDATE will return the #VALUE error if the start date is not a valid date.
- If the start date has a fractional time attached, it will be removed.
- If the months argument contains a decimal value, it will be removed.
- To return an end-of-month date, see the EOMONTH function .
- EDATE returns a date serial number , which must be formatted as a date .
