Formula Help "ERRORS"

Following Errors will be prompted when user enters wrong value, undetermined by Microsoft Excel




#DIV/0!

The formula attempts to divide by zero (an operation net allowed on this planet).

This also occurs when the formula attempts to divide by an empty cell

#NAME?

The formula uses a name that Excel doesn't recognize. This can happen if you

delete a name used in the formula or if you misspell a function.

#N/A

The formula refers (directly or indirectly) tea cell that uses the NA function to

signal unavailable data. This error also occurs if a lookup function does not find a
match.

#NULL!           

The formula uses an intersection of two ranges that don't intersect

#NUM!

A problem occurs with a value; for example, you specify a negative number where

a positive number is expected.

#REF!

The formula refers to an invalid cell. This happens if the cell has been deleted from

the worksheet.

#VALUE!

The formula includes an argument or operand of the wrong type. An operand

refers to a value or cell reference that a formula uses to calculate a result.

The WORKDAY ( ) Function

Adding days to or subtracting days from a date is straightforward, but the basic calculation includes all days: workdays, weekends, and holidays, rn many cases, you might need to ignore weekends and holJdays and return a date that is specified number of workdays from some original date.

You can do this by using the Analysis ToolPak's WORKDAY ( ) function, which returns a date

that is a specified number of working days from some starting dates.

Syntax:
WORKDAY( startdate, days[, holidays])
Start Date:
The original date (or a string representation of the date)
Days:
The number of workdays before or after start date. Use a positive number to return a later date; use a negative number to return an earlier date. Non-integer values are truncated (that is, the decimal part is ignored)

Holidays:

A list of dates to exclude from the calculation. This can be range of dates or an array constant (that is, a series of date serial numbers or date strings, separated by commas and surrounded by braces{ })

Example 1:
The following expression returns a date is 30 workdays from today: =WORKDAY(TODAY(),30)

Example2:

Note
here's another expression that returns the date that is 30 workdays from December 1, 2011, excluding December 25, 2011, and January 1,2012: =WORKDAY("11/2/2011",30,{"25/12/2011","1/1/2012"})

If you don't find this function in the list, lead the Analysis ToolPak add-in.

The WEEKDAY ( ) Function

The WEEKDAY ( ) function returns a number that corresponds to the day of the week upon which a specified date falls.

Syntax:  

WEEKDAY(serial‑number [, return‑type] )

Serial‑Number:

The date (or a string representation of the date) you want to work with

Return Type:  

An integer that determines how the value returned by WEEKDAY ( )

corresponds to the days of the week:

1. The return values are 1 (Sunday) through 7 (Saturday); this is the default.

2. The return values are 1 (Monday) through 7 (Sunday).

3.The return values are 0 (Monday) through 6 (Sunday).

Example: 

If you want to see the day of Sep. 20, 2011, type this formula in any
cell =WEEKDAY("9/20/2011")

Returns:                            4

The DAY ( ) Function

The DAY ( ) functiorl returns a number between 1 and 31 that corresponds to the day component of a specified date.

Syntax: 

DAY(serial‑number)

Serial‑number:

The date (or a string representation of the date) you want to work with.

Example:      

If today is Sep. 19,2011, type this formula Jn any cell
=DAY("9/19/2011")

Returns:                              19

The MONTH ( ) Function

The MONTH ( ) function returns a number between I and 12 that corresponds to the month component of a specified date.

Syntax:               MONTH(serial_number)

Serial‑number:  The date (or a string representation of the date) you want to  
                            work with.

Example:           if today is Sep.18,2011,type this formula in any cell

                           =MONTH("SEPTEMBER 18,2011")

Returns:            9

The YEAR ( ) Function

The YEAR ( ) function returns a four-digit number that corresponds to the year component of a specified date.

Syntax:   

YEAR(serial_number)

Serialnumber:

The date (or a string representation of the date) you want to work with.

Example:

If today is sep.17, 2011, type this formula in any cell =YEAR(TODAY ( ) )

Returns:  2011

Date Functions

The date and time functions enable you to convert dates and times to serial numbers and perform operations on those numbers. This capability is useful for such things as accounts receivable aging, project scheduling, time-management applications, and much more. This chapter introduces you to Excel's date and time functions and puts them through their paces with many practical examples.

Returning a Date

If you need a date for expression operand for a function argument, you can always enter it by hand if you have a specific date in mind. Much of the time, however, you need more flexibility, such as always entering the current date or building a date from day, month, and year components. Excel offers three functions that can help: TODAY( ), DATE( ), and DATE VALUE( ).

TODAY ( ): Returning the Current Date

 When you need to use the current date in a formula, function, or expression, use the TODAY ( ) function, which doesn't take any arguments:

Syntax:                                TODAY ( )

This function returns the serial number (or current date) of the current date. For example, if today date is December 31, 2007, the TODAY ( ) function returns the following serial number:

39447.0

Note that TODAY ( ) is a dynamic function that doesn't always return the same value. Each time you edit the formula, enter another formula, recalculate the worksheet, or reopen the workbook, TODAY ( ) updates its value to return the current system date.

DATE ( ): Returning Any Date

 A date consists of three components: the year, month, and day. It often happens that a worksheet generates one or more of these components, and you need some way of building a proper date out of them. You can do that by using Excel's DATE ( ) function:

Syntax:          DATE (year, month, day)

Year:             The year component of the date (a number between 1900 and  9999) 

Month:         month component of the date

Day:            The day component of the date

Type:          =DATE(2005, 8, 14) in any cell

Returns:     14-8-2005

DATEVALUE ( ) Converting a String to a Date

 If you have a date value in string form, you can convert it to a date serial number by using the DATE VALUE ( ) function:

Syntax:           DATEVALUE("date_text")

Date‑Text:    The string containing the date

Example:       The following expression returns the date serial number for the string August 23, 2007, Type
                       this formula =DATEVALUE("august 23, 2007")

Returns:                             39317

Returning Parts of a Date
The three components of a date - year, month, and day - can also be extracted individually from a given date. This might not seem all that interested at first, but actually many useful techniques arise out of working with a date's component pads. A date's components are extracted using Excel's YEAR (), MONTH (), and DAY
 ( ) functions.