YEARFRAC Function (Functions in Excel)

Start Date	End Date	Fraction
1-Jan-98	1-Apr-98	0.25	=YEARFRAC(C4,D4)
1-Jan-98	31-Dec-98	1	=YEARFRAC(C5,D5)
1-Jan-98	1-Apr-98	25%	=YEARFRAC(C6,D6)

What Does It Do?
This function calculates the difference between two dates and expresses the result
as a decimal fraction.
Syntax
=YEARFRAC(StartDate,EndData,Basis)
Basis : Defines the calendar system to be used in the function.
0 : or omitted USA style 30 days per month divided by 360.
1 : 29 or 30 or 31 days per month divided by 365.
2 : 29 or 30 or 31 days per month divided by 360.
3 : 29 or 30 0r 31 days per month divided by 365.
4 : European 29 or 30 or 31 days divided by 360.

Formatting

The result will be shown as a decimal fraction, but can be formatted as a percent.

Example

The following table was used by a company which hired people on short term contracts

for a part of the year.

The Pro Rata Salary which represents the annual salary is entered.

The Start and End dates of the contract are entered.

The =YEARFRAC() function is used to calculate Actual Salary for the portion of the year.

Start	End	Pro Rata Salary	Actual Salary
1-Jan-98	31-Dec-98	£12,000	£12,000	=YEARFRAC(B32,C32+1,4)*D32
1-Jan-98	31-Mar-98	£12,000	£3,000	=YEARFRAC(B33,C33+1,4)*D33
1-Jan-98	30-Jun-98	£12,000	£6,000	=YEARFRAC(B34,C34+1,4)*D34

Note

The extra 1 has been added to the End date to compensate for the fact that the =YEARFRAC()

function calculates from the Start date up to, but not including, the End date.

read more "YEARFRAC Function (Functions in Excel)"

YEAR Function (Functions in Excel)

Date	Year
25-Dec-98	1998	=YEAR(C4)

What Does It Do?

This function extracts the year number from a date.

Syntax

=YEAR(Date)

Formatting

The result is shown as a number.

read more "YEAR Function (Functions in Excel)"

WORKDAY Function (Functions in Excel)

StartDate	Days	Result
1-Jan-98	28	35836	=WORKDAY(D4,E4)
1-Jan-98	28	10-Feb-98	=WORKDAY(D5,E5)

What Does It Do?

Use this function to calculate a past or future date based on a starting date and a

specified number of days. The function excludes weekends and holidays and can

therefore be used to calculate delivery dates or invoice dates.

Syntax

=WORKDAY(StartDate,Days,Holidays)

Formatting

The result will normally be shown as a number which can be formatted to a

normal date by using Format,Cells,Number,Date.

Example

The following example shows how the function can be used to calculate delivery dates

based upon an initial Order Date and estimated Delivery Days.

Order Date	Delivery Days	Delivery Date
Mon 02-Feb-98	2	Wed 04-Feb-98
Tue 15-Dec-98	28	Tue 26-Jan-99
		=WORKDAY(D25,E25,D28:D32)

		Holidays
Bank Holiday		Fri 01-May-98
Xmas		Fri 25-Dec-98
New Year		Wed 01-Jan-97
New Year		Thu 01-Jan-98
New Year		Fri 01-Jan-99

read more "WORKDAY Function (Functions in Excel)"

WEEKDAY Function (Functions in Excel)

Date	Weekday
Thu 01-Jan-98	5	=WEEKDAY(C4)
Thu 01-Jan-98	5	=WEEKDAY(C5)
Thu 01-Jan-98	5	=WEEKDAY(C6,1)
Thu 01-Jan-98	4	=WEEKDAY(C7,2)
Thu 01-Jan-98	3	=WEEKDAY(C8,3)

What Does It Do?

This function shows the day of the week from a date.

Syntax

=WEEKDAY(Date,Type)

Type : This is used to indicate the week day numbering system.

1 : will set Sunday as 1 through to Saturday as 7

2 : will set Monday as 1 through to Sunday as 7.

3 : will set Monday as 0 through to Sunday as 6.

If no number is specified, Excel will use 1.

Formatting

The result will be shown as a normal number.

To show the result as the name of the day, use Format, Cells, Custom and set

the Type to ddd or dddd.

Example

The following table was used by a hotel which rented a function room.

The hotel charged different rates depending upon which day of the week the booking was for.

The Booking Date is entered.

The Actual Day is calculated.

The Booking Cost is picked from a list of rates using the =LOOKUP() function.

Booking Date	Actual Day	Booking Cost
7-Jan-98	Wednesday	£                 30.00
		=LOOKUP(WEEKDAY(C34),C39:D45)

Booking Rates
Day Of Week	Cost
1	£50
2	£25
3	£25
4	£30
5	£40
6	£50
7	£100

read more "WEEKDAY Function (Functions in Excel)"