On UrbanPro

View My Profile       Proud to be a Teacher       Write a Review  

Saturday, May 17, 2014

EDATE() function in MS-Excel


C
D
E

3
Start Date
Plus Months
End Date

4
1-Jan-98
3
1-Apr-98
 =EDATE(C4,D4)
5
2-Jan-98
3
2-Apr-98
 =EDATE(C5,D5)
6
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.



C
D
E

26
Start
Duration
End

27
Tue 06-Jan-98
3
Mon 06-Apr-98
 =EDATE(C27,D27)
28
Mon 12-Jan-98
3
Sun 12-Apr-98
 =EDATE(C28,D28)
29
Fri 09-Jan-98
4
Sat 09-May-98
 =EDATE(C29,D29)
30
Fri 09-Jan-98
3
Thu 09-Apr-98
 =EDATE(C30,D30)
31
Mon 19-Jan-98
3
Sun 19-Apr-98
 =EDATE(C31,D31)
32
Mon 26-Jan-98
3
Sun 26-Apr-98
 =EDATE(C32,D32)
33
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.



C
D
E
41
Start
Duration
End
42
Tue 06-Jan-98
3
Mon 06-Apr-98
43
Mon 12-Jan-98
3
Fri 10-Apr-98
44
Fri 09-Jan-98
4
Fri 08-May-98
45
Fri 09-Jan-98
3
Thu 09-Apr-98
46
Mon 19-Jan-98
3
Fri 17-Apr-98
47
Mon 26-Jan-98
3
Fri 24-Apr-98
48
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)


No comments:

Post a Comment

Popular Posts