Excel is a popular computer program that allows you to create and edit spreadsheets, which are used to store information in columns and rows that can then be organized and/or processed. Spreadsheets are designed to work well with numbers but often include text. Excel makes use of formulas (mathematical expressions that you create) and functions (mathematical expressions already available in Excel) to dynamically calculate results from data in your worksheets. Excel is included within the Microsoft Office Professional Edition (Academic Version).

Microsoft - Windows Marketplace

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

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"

Back to Top^

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)

Back to Top^

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)

Back to Top^

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 "Tools" menu and choose "Add-Ins...".
2. Check the box next to "Analysis Toolpak."
3. Click OK

Back to Top^

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))

Back to Top^

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)

Suppose the date in cell A1 is 01/15/2007, the formula would return 12/31/2006.

Back to Top^

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.

For this formula, we require that StartDate1 is less than (earlier than) or equal to EndDate1, and that StartDate2 is less than (earlier than) or equal to EndDate2. The formula below will return the number of days that are in both intervals.

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

The formula above does not treat weekend days differently from working days. In other words, Saturdays and Sundays are included in the calculations. If you want to count only weekdays, excluding weekends and holidays, use the modified version below, which calls the NETWORKDAYS function to compute the number of working days in the intervals. This function adds another name ranged to the mix. This name, NWRange, refers to a range containing a list of holidays. If you do not use holidays, you can either point this name to an empty cell, or eliminate it from the formula entirely.

=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 "Tools" menu and choose "Add-Ins...".
2. Check the box next to "Analysis Toolpak."
3. Click OK

Back to Top^

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 "Tools" menu and choose "Add-Ins...".
2. Check the box next to "Analysis Toolpak."
3. Click OK

Back to Top^

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)

Back to Top^

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.

Back to Top^

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.

Back to Top^

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))

Back to Top^

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)

The TEXT function converts a number to text, and will work correctly even if the lookup values are text. In the first example above, the & operator creates a text string from an unformatted number. In the second example, a number formatted with leading zeros (e.g. 00123) would match a text "number" with leading zeros.

Back to Top^