Browse All Articles > MS Excel: Underused formulas you should know

With the growth of technology and the relative ease of use of programs such as Microsoft Excel, users are finding it more efficient to document and perform calculations.

With the growth of technology and the relative ease of use of programs such as Microsoft Excel, users are finding it more efficient to document and perform calculations. If you were to open an Excel file, the odds are that you will see a variety of formulas ranging from the basic addition (**+**) and subtraction (**-**) mathematical calculations to the more complex **vlookup** and **hlookup** logical formulas. With its growing popularity, Excel is constantly raising the bar and setting the standards for included functions and formulas with each new release.

Despite the popularity of Excel and the Formula Bar which guides you to creating those formulas, there are a few uncommon formulas that could ease your spreadsheet and calculation tasks. Feel free to grab the attached workbook and follow along!

**DATEDIF**

The**DATEDIF** formula is used to calculate the difference between two dates, using a set interval.

__Use:__

**=DATEDIF(***date1,date2,interval*)

*date1* is the starting date

*date2* is the ending date

Intervals can be any value from the following:

*d* – Days

*m* – Months

*y* – Years

*yd* – Days, excluding Years

*ym* – Months, excluding Years

*md* – Days, excluding Months and Years

__Examples:__

Start Date (cell E7) is 1/15/2011

End Date (cell E7) is 6/26/2013

**=DATEDIF(E7, E8,”d”)** would return a value of 893, representing 893 Days

**=DATEDIF(E7, E8,”m”)** would return a value of 29, representing 29 Months

**=DATEDIF(E7, E8,”y”)** would return a value of 2, representing 2 Years

**=DATEDIF(E7, E8,”yd”)** would return a value of 162, representing 162 Days, excluding Years (1/15 to 6/26)

**=DATEDIF(E7, E8,”ym”)** would return a value of 5, representing 5 Months, excluding Days and Years (Jan to Jun)

**=DATEDIF(E7, E8,”md”)** would return a value of 11, representing 11 Days, excluding Months and Years (15th to the 26th)

**CONVERT**

The CONVERT formula is used to translate a measurement system into another measurement.

__Use:__

**=CONVERT(***number,from_unit,to_unit*)

Number is any numerical value.

The from_unit and to_units can be any form of measurement from within a category (Liquid Measurement, Force, Weight, etc.) to another unit in the same category.

For a complete listing of units, please visit the Microsoft Office Support pages.

__Examples:__

In cell E23 is the value 30.

To convert Inches to Feet, we would use**=CONVERT(A1,”in”,”ft”)** which returns a value of 2.5, representing 2.5 Feet in 30 inches.

To convert Hours to Seconds, we would use**=CONVERT(A1,"hr","sec")** which returns a value of 108000, representing 108,000 seconds in 30 hours.

To convert Inches to Meters, we would use**=CONVERT(A1,"in","m")** which returns a value of 0.762, representing 0.762 meters in 30 inches.

If you are following along in the provided workbook, you should note that the formula used in E24 contains cell references in place of the measurement unit values as described herein.

**REPT**

The REPT formula is used to repeat a value a given number of times.

__Use:__

**=REPT(***value,number_of_times*)

Value is any text or numerical string value or cell reference to a any text or numerical string.

Number_of_times is any numerical value.

__Examples:__

In cell E36 is the value abc

In cell E37 is the value 4

**=REPT(E36,E37)** would return the string: abcabcabcabc

**SIGN**

The**SIGN** formula is used to determine the (sign) of a number, either positive (1), negative (-1) or zero (0).

__Use:__

**=SIGN(***number*)

Number is any numerical value.

__Examples:__

In cell E45 is the value: -351

**=SIGN(E45)** would return the value: -1 (meaning the number is negative)

If in cell E45, we use the formula:**=(-4*19)/(2*-6)*4**

**=SIGN(E45)** would return the value: 1 (meaning the resulting value is positive)

**DOLLAR & USDOLLAR**

The DOLLAR and USDOLLAR formulas are used to convert a number to currency format, with decimal rounding.

While the formulas work the same in functionality, they**DOLLAR** formula will use the local PC currency setting while the **USDOLLAR** formula will always be formatted to US $.

__Use:__

**=DOLLAR(***number,number_of_decimals*)

**=USDOLLAR(***number,number_of_decimals*)

Number is any numerical value.

Number_of_Decimals is any numerical value.

__Examples:__

In cell E53 is the value: 8596.1652

In cell E54 is the value: 2

**=USDOLLAR(E53,E54)** would return the value: $8,596.17

In cell E63 is the value: 456.256548

In cell E64 is the value: 4

**=USDOLLAR(E63,E64**) would return the value: $456.2565

While sticking to your normal formulas is always a safe fallback, don’t be afraid to get out there and experiment! Your Excel Spreadsheet is only as advanced as the formulas and functions used to make it; so feel free to dive deeper into those Excel formulas and if needed, post a Question here on Experts-Exchange for any clarification or help that may be needed.

Despite the popularity of Excel and the Formula Bar which guides you to creating those formulas, there are a few uncommon formulas that could ease your spreadsheet and calculation tasks. Feel free to grab the attached workbook and follow along!

The

Intervals can be any value from the following:

Start Date (cell E7) is 1/15/2011

End Date (cell E7) is 6/26/2013

The CONVERT formula is used to translate a measurement system into another measurement.

Number is any numerical value.

The from_unit and to_units can be any form of measurement from within a category (Liquid Measurement, Force, Weight, etc.) to another unit in the same category.

For a complete listing of units, please visit the Microsoft Office Support pages.

In cell E23 is the value 30.

To convert Inches to Feet, we would use

To convert Hours to Seconds, we would use

To convert Inches to Meters, we would use

If you are following along in the provided workbook, you should note that the formula used in E24 contains cell references in place of the measurement unit values as described herein.

The REPT formula is used to repeat a value a given number of times.

Value is any text or numerical string value or cell reference to a any text or numerical string.

Number_of_times is any numerical value.

In cell E36 is the value abc

In cell E37 is the value 4

The

Number is any numerical value.

In cell E45 is the value: -351

If in cell E45, we use the formula:

The DOLLAR and USDOLLAR formulas are used to convert a number to currency format, with decimal rounding.

While the formulas work the same in functionality, they

Number is any numerical value.

Number_of_Decimals is any numerical value.

In cell E53 is the value: 8596.1652

In cell E54 is the value: 2

In cell E63 is the value: 456.256548

In cell E64 is the value: 4

While sticking to your normal formulas is always a safe fallback, don’t be afraid to get out there and experiment! Your Excel Spreadsheet is only as advanced as the formulas and functions used to make it; so feel free to dive deeper into those Excel formulas and if needed, post a Question here on Experts-Exchange for any clarification or help that may be needed.

Get access with a 7-day free trial.

SIGN UP

Not ready to sign up? Try a week for free to see if you belong

## Comments (9)

Commented:

Commented:

I never knew of the datedif function. I've added it to my cheat sheet

Commented:

Start Date (cell E7) is 1/15/2011End Date (cell E7) is 6/26/2013

I'd say that you're End Date cell is cell E8

Commented:

So in my case it's like this: =DATUMVERSCHIL(e7;e8;"d")

Commented:

View More