DatedIF Formula

This function calculates the difference between two dates. It can show the result in weeks, months or years.

	FirstDate	SecondDate	Interval	Difference
	1-Jan-60	10-May-70	days	3782	=DATEDIF(C4,D4,"d")
	1-Jan-60	10-May-70	months	124	=DATEDIF(C5,D5,"m")
	1-Jan-60	10-May-70	years	10	=DATEDIF(C6,D6,"y")
	1-Jan-60	10-May-70	yeardays	130	=DATEDIF(C7,D7,"yd")
	1-Jan-60	10-May-70	yearmonths	4	=DATEDIF(C8,D8,"ym")
	1-Jan-60	10-May-70	monthdays	9	=DATEDIF(C9,D9,"md")
What Does It Do?
This function calculates the difference between two dates.
It can show the result in weeks, months or years.
Syntax
=DATEDIF(FirstDate,SecondDate,"Interval")
FirstDate : This is the earliest of the two dates.
SecondDate : This is the most recent of the two dates.
"Interval" : This indicates what you want to calculate.
These are the available intervals.
	"d"	Days between the two dates.
	"m"	Months between the two dates.
	"y"	Years between the two dates.
	"yd"	Days between the dates, as if the dates were in the same year.
	"ym"	Months between the dates, as if the dates were in the same year.
	"md"	Days between the two dates, as if the dates were in the same month and year.
Formatting
No special formatting is needed.
	Birth date :	1-Jan-60
	Years lived :	51	=DATEDIF(C8,TODAY(),"y")
	and the months :	8	=DATEDIF(C8,TODAY(),"ym")
	and the days :	24	=DATEDIF(C8,TODAY(),"md")
	You can put this all together in one calculation, which creates a text version.
	Age is 51 Years, 8 Months and 24 Days
	="Age is "&DATEDIF(C8,TODAY(),"y")&" Years, "&DATEDIF(C8,TODAY(),"ym")&" Months and "&DATEDIF(C8,TODAY(),"md")&" Days"

Date Formula

This function creates a real date by using three normal numbers typed into separate cells.

	Day	Month	Year	Date
	25	12	99	12/25/99	=DATE(E4,D4,C4)
	25	12	99	25-Dec-99	=DATE(E5,D5,C5)
	33	12	99	January 2, 2000	=DATE(E6,D6,C6)
What Does It Do?
This function creates a real date by using three normal numbers typed into separate cells.
Syntax
=DATE(year,month,day)
Formatting
The result will normally be displayed in the dd/mm/yy format.
By using the Format,Cells,Number,Date command the format can be changed.

CountIF Formula

This function counts the number of items which match criteria set by the user.

	Item	Date	Cost
	Brakes	1-Jan-98	80
	Tyres	10-May-98	25
	Brakes	1-Feb-98	80
	Service	1-Mar-98	150
	Service	5-Jan-98	300
	Window	1-Jun-98	50
	Tyres	1-Apr-98	200
	Tyres	1-Mar-98	100
	Clutch	1-May-98	250
How many Brake Shoes Have been bought.				2	=COUNTIF(C4:C12,"Brakes")
How many Tyres have been bought.				3	=COUNTIF(C4:C12,"Tyres")
How many items cost £100 or above.				5	=COUNTIF(E4:E12,">=100")
Type the name of the item to count.			service	2	=COUNTIF(C4:C12,E18)
What Does It Do ?
This function counts the number of items which match criteria set by the user.
Syntax
=COUNTIF(RangeOfThingsToBeCounted,CriteriaToBeMatched)
The criteria can be typed in any of the  following ways.
To match a specific number type the number, such as =COUNTIF(A1:A5,100)
To match a piece of text type the text in quotes, such as =COUNTIF(A1:A5,"Hello")
To match using operators surround the expression with quotes, such as =COUNTIF(A1:A5,">100")
Formatting
No special formatting is needed.