EDATE function | Excel Formula Calculates Due Date

datetimeexcel.blogspot.com - The EDATE function is one of the functions used to calculate the due date, so that the output of this function is the same date on the specified due month. The due date can be set in the following months or several months in advance. Here's a tutorial on how to use the EDATE function.

A. How to Use the EDATE Function

The following is the systematics of writing the EDATE function in Excel,

= EDATE ( start_data , months )

  • start_date is the start date of the EDATE function which can be written directly or in the form of a reference to the cell address containing the date data.
  • Start_date  that is defined directly can use the DATE function or in quotes.
    1. With the DATE function 
    2. By writing in quotation marks, for example: December 31, 2018, it is written "31-Dec-2018"
  • months  is the number of months due to be calculated, in the form of numeric values.
    1. Positive number  for the following month, example 2 for the next 2 months.
    2. Negative number  for the previous month, example -1 for 1 month ago.
  • If the number of dates in the result is less than the start date, the last date of the resulting month is used.
  • The result of the calculation is in the form of an Excel serial number, so it can cause the date format to be a regular number.

IMPORTANT : The use of  commas (,)  or  semicolon (;)  in the  FORMULA is  adjusted to the version of Microsoft Excel and the language configuration on your computer.

B. Serial Number in Excel

The serial number generated by date functions is a standard Microsoft Excel numeric value. This serial number is used by Microsoft Excel to perform arithmetic calculations, logic, and Visual Basic programming. The serial number sequence by date starts from 1st January 1900 with 1 day intervals, i.e. bandarq online

1-Jan-1900 = 1
2-Jan-1900 = 2
….
31-Jan-2019 = 43496
and so on

C. Example Using the EDATE Function

1. Calculate by direct date

Here's how to calculate the due date with direct date definitions in the EDATE formula that is formed.

Example: For example, it will be calculated due to the following 3 months with a starting date of January 31, 2019.

The following formulas can be used,

= EDATE ( "31-Jan-2019" , 3 )

How to use the EDATE function

The result that is issued by default is a serial number. So it is necessary to change the following cell format,

How to change serial number with EDATE in Excel
How to change serial number with EDATE in Excel

The illustration above is how to change the Excel serial number format to the date format ( Short Date ). So that the EDATE function output changes to 4/30/2019 or April 30, 2019 .

2. Calculation of the due date with cell references

Here's an example of using the EDATE function to determine a due date.

Looks for due dates with positive arguments (next month)

In the following example an Excel formula will be created for a reminder due to 1 month and 3 months in the future, in the following table:

calculation of the due date

Excel formulas can be created

= EDATE ( A2 , B2 )

Followed by autofill up to the third line,

How to calculate the due date with the EDATE function

So that the stairs 29-Feb-19 and 24-Apr-19 are obtained.

Counting stairs to maturity with negative arguments (past months)

For example, we will calculate the due date, if it is known that the end date is February 18, 2019 with a period of 4 months.

Calculate the due date

So that the EDATE formula can be used with the month argument  worth -4,

= EDATE ( A2 , -4 )

Calculate the due date with the EDATE function

So that the start date is obtained October 18 2018

Komentar

Posting Komentar

Postingan populer dari blog ini

7 Ways to Make Auto Date Formulas in Excel and Examples

Gambar
datetimeexcel.blogspot.com -  There are 7 formulas for creating automatic dates in Microsoft Excel, namely the TODAY, NOW, DATE, MONTH, DAY, YEAR, and DAYS formulas.  Fundamentally, to perform calculations Excel will convert date data into numeric data, so that it can be processed when creating automatic date formulas in Excel.  The following is a brief explanation in the following table. Excel formulas Uses TODAY () Today's date (dynamic) Excel formula NOW () Today's time and date Excel formula DATE Excel formulas write dates MONTH Excel formula to take the month in a cell DAY Excel formula to retrieve the date in a cell YEAR Excel formula to take the year in a cell DAYS Excel formulas calculate the difference between two dates CTRL  +  ; Shortcut to insert today's date (static) A. Excel TODAY () formula The excel formula function TODAY () is to retrieve today's data from the internal date system on the computer being used.  The TODAY () formula does not have any argu
Baca selengkapnya

Excel's DATEVALUE function and examples of its use

Gambar
datetimeexcel.blogspot.com -  The DATEVALUE function is a date function in Microsoft Excel that is used to convert date text data to an Excel serial number.  This serial number is recognized by Microsoft Excel as a numeric value so that it can be applied to other Excel functions, such as logical and Visual Basic functions. A. How to Use the DATEVALUE function in Excel The following is the systematics of writing the DATEVALUE function, = DATEVALUE ("  Date_text  ") Date_text   is the date text data to be converted. Date_text  can only be written directly in quotation marks. The  Date_text   writing   format follows the default date format in Microsoft Excel. Format  mm / dd / yy  , for example 31 January 2019 is "1/31/2019" Format   dd-mm-yy  , for example 31 January 2019 is "31-Jan-2019" Output is a numeric value which is the Excel serial number. B. Serial Number in Excel The serial number generated by date functions is a standard Microsoft Excel numeric
Baca selengkapnya