## Excel Formulas & Functions

This area of the web-site is specific to Excel formulas and functions and their daily use. Click on the bulleted items below to view Excel formulas and functions.

- Age based on date of birth
- Date and Time: Add days, months or years to a date
- Date and Time: Calculate difference between two times
- Date and Time: Calculate working hours
- Date and Time: Date falls within an interval
- Date and Time: Last day of a previous Month
- Date and Time: Number of days common to two intervals
- Date and Time: Number of workdays between two dates
- Date and Time: Parse date and time
- IF Function
- Trim Function
- VLOOKUP: Eliminating #N/A error result
- VLOOKUP: Troubleshooting Text vs. Number

##### Age based on date of birth

You can use the formula below to return the age of any person in years, months and days. It makes use of the little known DATEDIF Function.

The syntax for the DATEDIF function is as follows:

=DATEDIF(Start_Date,End_Date,Unit) |

Where *Start_Date* is the person's date of birth (DOB),
*End_Date* is the date of when you want to know what age
the person was on this specific date (usually the *End_Date*
is substituted with the formula =TODAY(), which would give you
the exact age each time the worksheet is opened, and *Unit*
is either stated in days ("D"), months ("M"), or years ("Y").
"YM" (The months and years of both dates are ignored) ,"YD"
(The days and years of both dates are ignored) and "MD" (The
years of both dates are ignored) can also be used within the
DATEDIF Function.

Thus, you could string together a formula to return the age of any person in years, months and days as follows:

=DATEDIF(A1,TODAY(),"Y") & " Years, " & DATEDIF(A1,TODAY(),"YM")
& " Months, " & DATEDIF(A1,TODAY(),"MD") & " Days" |

##### Date and Time: Add days, months or years to a date

The following formula will add one year to the date entered within Cell A1:

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) |

The following formula will add one month to the date entered within Cell A1:

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) |

The following formula will add one day to the date entered within Cell A1:

=DATE(YEAR(A1),MONTH(A1),DAY(A1)+1) |

##### Date and Time: Calculate difference between two times

The following formulas present the result in a total based on one time unit (120 minutes). To do this task, use the INT function, or HOUR, MINUTE, and SECOND functions.

There are several ways to calculate the difference between two times. Suppose cell (A2) contains a begin time of 6/9/2007 10:35 AM and cell (B2) contains and end time of 6/9/2007 3:30 PM.

To calculate the total hours (answer: 28) between cell (A2) and cell (B2):

=INT((B2-A2)*24) |

To calculate the total minutes (answer: 1735) between cell (A2) and cell (B2):

=(B2-A2)*1440 |

To calculate the total seconds (answer: 104100) between cell (A2) and cell (B2):

=(B2-A2)*86400 |

To calculate the total hours (answer: 4) between cell (A2) and cell (B2) when the difference does not exceed 24 hours:

=HOUR(B2-A2) |

To calculate the total minutes (answer: 55) between cell (A2) and cell (B2) when the difference does not exceed 60 minutes:

=MINUTE(B2-A2) |

To calculate the total seconds (answer: 0) between cell (A2) and cell (B2) when the difference does not exceed 60 seconds:

=SECOND(B2-A2) |

##### Date and Time: Calculate working hours

How to calculate working hours from 1/27/2004 11:26:00 AM
(A1) to 1/27/2004 4:35:00 PM (B1)?

For calculation of working hours, use:

=((NETWORKDAYS(A1,B1)-2)*8/24)+(TIME(17,0,0)-MOD(A1,1))+(MOD(B1,1)-TIME(9,0,0)) |

Custom format the working hours as [hh]:mm

**IMPORTANT!**: For this formula to work, you have to
first add in this formula to the default Excel installation.
Do that by following these steps:

1. Go to the "__T__ools" menu and choose "Add-__I__ns...".

2. Check the box next to "Analysis Toolpak."

3. Click OK

##### Date and Time: Date falls within an interval

Does a specified date (TDATE) fall within a given time interval (START_DATE) and (END_DATE)?

=AND((TDATE>=MIN(START_DATE,END_DATE)),TDATE<=MAX(START_DATE,END_DATE)) |

##### Date and Time: Last date of previous month

You can use the following formula to get the date of the last day of the previous month.

=DATE(YEAR(A1),MONTH(A1),0) |

##### Date and Time: Number of days common to two intervals

Do two specified intervals share common days? For this formula, we will have 4 named cells, as shown below:

Name |
Description |

StartDate1 | The starting date of the first interval. |

EndDate1 | The ending date of the first interval. |

StartDate2 | The starting date of the second interval. |

EndDate2 | The ending date of the second interval. |

NWRange | A list of holiday dates. Used in the second version of the formula, which uses the NETWORKDAYS function. |

=IF(OR(EndDate1<StartDate2,StartDate1>EndDate2),0,(MIN(EndDate1,EndDate2)-MAX(StartDate1,StartDate2)+1)) |

=IF(OR(EndDate1<StartDate2,StartDate1>EndDate2),0, ABS(NETWORKDAYS(MIN(EndDate1,EndDate2),MAX(StartDate1,StartDate2),NWRange))) |

**IMPORTANT!**: For this formula to work, you have to first add in this formula to the default Excel installation. Do that by following these steps:

1. Go to the "

__T__ools" menu and choose "Add-

__I__ns...".

2. Check the box next to "Analysis Toolpak."

3. Click OK

##### Date and Time: Number of workdays between two dates

NETWORKDAYS : This function calculates the number of weekdays between two given dates. Then it will subtract the holidays you feed into it from the total. The first date ("07/01/2006") is the start date. The second is the end date ("09/30/2006"), and the last section ({"07/04/2006","09/04/2006"}) is where you put in the holidays.

=NETWORKDAYS(DATEVALUE("07/01/2006"),DATEVALUE("09/30/2006"),DATEVALUE({"07/04/2006","09/04/2006"})) |

**IMPORTANT!**: For this formula to work, you have to first add in this formula to the default Excel installation. Do that by following these steps:

1. Go to the "

__T__ools" menu and choose "Add-

__I__ns...".

2. Check the box next to "Analysis Toolpak."

3. Click OK

##### Date and Time: Parse date and time

How to parse (separate) date and time from 1/27/2004 11:26:00
AM?

To obtain the date, use:

=INT(A1) |

For the time, use:

=MOD(A1,1) |

##### IF Function

The IF Function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE. The syntax for the If function is:

=IF(logical_test, value_if_true, value_if_false) |

Where...

*logical_test*is the value that you want to test.*value_if_true*is the value that is returned if condition evaluates to TRUE.*value_if_false*is the value that is returned if condition evaluates to FALSE.

Example:

If...

Cell A1 = 30 &

Cell A2 contains the text "Tech on the Net"

Then...

=If(A1>10, "Larger", "Smaller") would return "Larger".

=If(A1=20, "Equal", "Not Equal") would return "Not Equal".

=If(A2="Tech on the Net", 12, 0) would return 12.

##### Trim Function

the **Trim** function returns a text value with the
leading and trailing spaces removed. The syntax for the **
Trim** function is:

=Trim(text) |

Where text is the text value to remove the leading and
trailing spaces from.

##### VLOOKUP: Eliminating #N/A error result

By using the ISNA function, any cell with an invalid VLOOKUP reference will yield the dreaded #N/A result. To eliminate the #N/A error, use the VLOOKUP formula within the IF Function below:

=IF(ISNA(VLOOKUP(lookup_value, table_array,
col_index_num, FALSE)=TRUE),"",VLOOKUP(lookup_value,
table_array, col_index_num, FALSE)) |

##### VLOOKUP: Troubleshooting Text vs. Number

Even though the value you're looking for appears to be in the lookup table, the VLOOKUP formula returns an #N/A. A common cause for this error is that one of the values is a number, and the other is text. Convert the text to numbers directly in your VLOOKUP formula. If the lookup table contains numbers, and the value to look up is text, use a formula similar to the following:

=VLOOKUP(--A5,Employees!$A$1:$C$6,2,FALSE) |

The double unary (--) above converts text to a number, and will work correctly even if the lookup values are numbers.

If the lookup table contains text, and the value to look up is numeric, use a formula similar to the following:

=VLOOKUP(A5 & ""),Employees!$A$1:$C$6,2,FALSE) OR =VLOOKUP(TEXT(A5,"00000"),Employees!$A$1:$C$6,2,FALSE) |