EDate Formula
|
|
Start Date
|
Plus Months
|
End Date
|
|
|
|
1-Jan-98
|
3
|
1-Apr-98
|
=EDATE(C4,D4)
|
|
|
2-Jan-98
|
3
|
2-Apr-98
|
=EDATE(C5,D5)
|
|
|
2-Jan-98
|
-3
|
2-Oct-97
|
=EDATE(C6,D6)
|
|
|
|
|
|
|
|
What Does It Do?
|
|
|
|
|
|
This
function is used to calculate a date which is a specific number of months in
the past or
|
||||
|
in the
future.
|
|
|
|
|
|
|
|
|
|
|
|
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.
|
|
|||
|
The =EDATE()
function has been used to calculate the end of the contract.
|
||||
|
|
|
|
|
|
|
|
Start
|
Duration
|
End
|
|
|
|
Tue 06-Jan-98
|
3
|
Mon 06-Apr-98
|
=EDATE(C27,D27)
|
|
|
Mon 12-Jan-98
|
3
|
Sun 12-Apr-98
|
=EDATE(C28,D28)
|
|
|
Fri 09-Jan-98
|
4
|
Sat 09-May-98
|
=EDATE(C29,D29)
|
|
|
Fri 09-Jan-98
|
3
|
Thu 09-Apr-98
|
=EDATE(C30,D30)
|
|
|
Mon 19-Jan-98
|
3
|
Sun 19-Apr-98
|
=EDATE(C31,D31)
|
|
|
Mon 26-Jan-98
|
3
|
Sun 26-Apr-98
|
=EDATE(C32,D32)
|
|
|
Mon 12-Jan-98
|
3
|
Sun 12-Apr-98
|
=EDATE(C33,D33)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The
company decide not to end contracts on Saturday or Sunday.
|
||||
|
The
=WEEKDAY() function has been used to identify the actaul 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 a Friday.
|
|
|||
|
|
|
|
|
|
|
|
Start
|
Duration
|
End
|
|
|
|
Tue 06-Jan-98
|
3
|
Mon 06-Apr-98
|
|
|
|
Mon 12-Jan-98
|
3
|
Fri 10-Apr-98
|
|
|
|
Fri 09-Jan-98
|
4
|
Fri 08-May-98
|
|
|
|
Fri 09-Jan-98
|
3
|
Thu 09-Apr-98
|
|
|
|
Mon 19-Jan-98
|
3
|
Fri 17-Apr-98
|
|
|
|
Mon 26-Jan-98
|
3
|
Fri 24-Apr-98
|
|
|
|
Mon 12-Jan-98
|
3
|
Fri 10-Apr-98
|
|
|
|
|
|
|
|
|
=EDATE(C48,D48)-IF(WEEKDAY(EDATE(C48,D48),2)>5,WEEKDAY(EDATE(C48,D48),2)-5,0)
|
||||