EDATE

The EDATE function returns a date that is “X” number of months before or after a given date.

EDATE(start_date, months)

start-date:  The starting date. start-date is a date value.

months: The number of months before or after the starting date. months is a number value. A negative months is used to specify a number of months before the starting date and a positive months is used to specify a number of months after the starting date.

Example 1:

Suppose we have to issue bank guarantee for a period of 24 month from current date of 13-APR-2012

to calculate the validity period we use the EDATE function as shown in the image below:


Example 2:

the bill due date in 2 months from the current date which is 10-APR-2012 . if the due date happens to be saturday and sunday then the due date will be last Friday.

We can solve this using the Weekday function and edate function. the formulas are clearly explained in the below image.

 

the formula in cell C1 is

=EDATE(A1,B1)-IF(WEEKDAY(EDATE(A1,B1),2)>5,WEEKDAY(EDATE(A1,B1),2)-5,0)

The =WEEKDAY() function has been used to identify the actual weekday number of the end date. If the weekday number is 6 or 7, (Sat or Sun), then 5 is subtracted from the =EDATE() to ensure the end of contract falls on a Friday.

WEEKDAY

WEEKDAY
The WEEKDAY function returns a number that is the day of the week for a given date.

WEEKDAY(number, type)

number:  The date the function should use. date is a date/time value. The time portion
is ignored by this function.

type: An optional value that specifies how days are numbered.

Sunday is 1 (1 or omitted):  Sunday is the first day (day 1) of the week and Saturday
is day 7.
Monday is 1 (2):  Monday is the first day (day 1) of the week and Sunday is day 7.
Monday is 0 (3):  Monday is the first day (day 0) of the week and Sunday is day 6.

Examples
=WEEKDAY(“2-Feb-2012”, 1) returns 5  (Thursday, the fifth day if you start counting Sunday as day 1).
=WEEKDAY(“2-Feb-2012”) returns the same value as the preceding example (numbering scheme 1 is used if no number-scheme argument is specified).

=WEEKDAY(“2-Feb-2012”,2) returns 4 (Thursday, the Fourth day if you start counting Monday as day 1).
=WEEKDAY(”2-Feb-2012”, 3) returns 3 (Thursday, day number 3 if you start counting Monday as day 0).