Saturday, May 17, 2014

EDATE() function in MS-Excel

 
EDATE Function Example1 Image

What Does It Do?

In the Excel EDATE function returns a date on the same day of the month, N months in the past or future. EDATE function can be used to calculate the expiration date, the maturity date, and the other due date. 

Note: Use a positive value for months to get a date in the future, and a negative value for dates in the past.

Syntax:

    =EDATE(StartDate,Months)


Formatting:

The result will normally be expressed as a number, this can be formatted to represent a date by using the Format > Cells > Number > Date command.

Example:

This example was used by a company hiring contract staff. The company needed to know the end date of the employment. The Start date is entered. The contract Duration is entered as months, and the =EDATE() function has been used to calculate the end of the contract.

EDATE Function Example2 Image

The company has decided not to end the contracts on Saturday or Sunday.
The =WEEKDAY() function has been used to identify the actual weekday number of the end date.
If the week day number is 6 or 7, (Sat or Sun), then 5 is subtracted from the =EDATE() to ensure the end of contract falls on any of the working day but not on Saturday or Sunday.

EDATE Function Example3 Image

Giving you the function entered in the cell E48 for your reference. You can use the same function in rest of the cells as well of column E as shown in the above example.

    =EDATE(C48,D48)-IF(WEEKDAY(EDATE(C48,D48),2)>5,WEEKDAY(EDATE(C48,D48),2)-5,0)


No comments:

Post a Comment