N()

Syntax. N(value)

Definition. This function returns a value converted into a number.

Arguments

• value (required). The expression (a number, text, a formula without an equal sign, a logical value, an error value, a reference, or a name) that you want to convert into a number

Background. Usually you don’t have to use the N() function, because Excel converts values automatically if a number is required. The IS() functions are an exception. To provide compatibility with other spreadsheet software, this function is still available.

The N() function converts expressions as shown in Table 11-6.

Table 11-6. Conversion Results with the N() Function

Argument (Expression)	Return Value
Number	This number
Date	Consecutive number for the date
Time	Number for the time
TRUE	1
FALSE	0
Error value	This error value
Text or an empty cell	0

Inside Out: Time basics

Excel saves dates as consecutive numbers. With the default setting, January 1, 1900, and also January 1, 1900, 12:00 AM have the value 1. Decimal numbers for days are saved in fractions of 1. The time 12:00 P.M. has the value 0.5; 6:00 P.M. has the value 0.75; and 3:46 A.M. has the value 0.15694444. Therefore, January 1, 1900, at 12:00 P.M. has the value 1.5; January 2, 1900, at 6:00 P.M. has the value 2.75; and so on. Decimal numbers without an integer part can be formatted as dates but are assigned the nonexistent date of January 0, 1900, instead of December 31, 1899.

To change the default setting of the first day, on the Advanced tab of the Excel Options dialog box, select Use 1904 Date System (in Excel 2007 and Excel 2010) or select Tools/Options and then Use 1904 Date System (in previous versions of Excel). January 1, 1904, at 0:00 returns the value 0, and January 2, 1904, at 0:00 returns the value 1. Use this option with care, because all date values are changed.

Example. For examples of the use of this function, see the “Time Basics” Inside Out sidebar in this chapter.

See Also

T()

Sample Files

Use the Misc worksheet in the Information.xls or Information.xlsm sample file. The sample files are found in the Chapter11 folder. For more information about the sample files, see the section titled Using the Sample Files.