Date, maths, text and logical functions

Dates are stored as serial numbers, counted from January 1, 1900, which is serial 1. So January 1, 2009 is 39814. That is why you can subtract one date from another.

• TODAY() gives today's date; NOW() gives date and time.
• DAY, MONTH and YEAR pull out one part of a date.
• DATEVALUE("16-04-2009") turns text into a date serial: 39919.
• Age in years = (today − birth date) / 365.25.

Maths functions:

• SUMIF(range, criteria, sum_range) adds only the cells that meet a condition.
• ROUND(number, digits) rounds normally: ROUND(2.149,1) = 2.1, and a negative digit rounds to the left of the point: ROUND(21.5,-1) = 20.
• ROUNDUP always rounds away from zero; ROUNDDOWN always towards zero.

Counting: COUNT counts cells with numbers; COUNTA counts non-blank cells; COUNTBLANK counts blanks; COUNTIF(range, criteria) counts cells that meet a condition. ROWS and COLUMNS count the rows and columns in a range.

Text: TEXT(value, format) turns a number into formatted text: =TEXT(L1,"Rs. 0.00") shows Rs. 23.50. CONCATENATE joins pieces of text, such as first, middle and surname into a full name.

Logical: AND is TRUE only if all its arguments are TRUE; OR is TRUE if any one is TRUE. Inside an IF they test several conditions at once: =IF(AND(A2>1,A2<100), A2, "out of range").