Microsoft Office Tutorials and References
In Depth Information
Working with specialized date functions
falls exactly 23 months after June 12, 2014, type the formula =EDATE("6/12/2014", 23) ,
which returns the value 42502 , or May 12, 2016.
The EOMONTH function returns a date that is an indicated number of months before or
after a given date. Although EOMONTH is similar to EDATE and takes the same arguments,
the value returned is always rounded up to the last day of the month. For example, to
calculate the serial date value that is the last day of the month 23 months after June 12, 2014,
type the formula =EOMONTH("6/12/2014", 23) , which returns 42521 , or May 31, 2016.
Using the YEARFRAC function
The YEARFRAC function calculates a decimal number that represents the portion of a year
that falls between two given dates. This function takes the arguments ( start_date , end_date ,
basis ), where start_date and end_date specify the period of time you want to convert to a
fractional year. The basis argument is the type of day count you want to use, as described in
Table 15-3.
For example, to determine what fraction of a year is represented from 4/12/10 to 12/15/10,
you can type the formula =YEARFRAC("4/12/14", "12/15/14") . This formula returns
0.675 based on the default 30-day month and 360-day year.
TABLE 15-3 Basis codes
If basis is YEARFRAC returns
0 (or omitted)
30/360, or 30 days per month and 360 days per year, as established in the
United States by the National Association of Security Dealers (NASD)
Actual/actual, or the actual number of days in the month(s)/actual days in
the year
1
Actual/360
2
Actual/365
3
European 30/360
4
Using the WORKDAY, NETWORKDAYS, WORKDAY.INTL, and
NETWORKDAYS.INTL functions
The WORKDAY and NETWORKDAYS functions are invaluable for anyone who calculates
payroll and benefits or determines work schedules. Both functions return values based on
working days, excluding weekend days. In addition, you can choose whether to include
holidays and specify the exact dates. The .INTL forms of these two functions include an
additional argument you can use to specify exactly what constitutes a weekend.
The WORKDAY function returns the date that is an indicated number of working days
before or after a given date. This function takes the arguments ( start_date , days , holidays ),
where start_date is the date you want the function to count from and days is the number
Search JabSto ::




Custom Search