The convert function is used to change data from one unit type to another in Excel. It is an engineering function and can be located in version 2010 on the Formulas section of the ribbon as follows; More functions>Engineering>convert.

When I first started using this function, I was converting dates and times and had no idea there was such a huge range of units that could be converted with this functionality. This article will explore a range of conversion units. If you are looking for professional Excel courses, have a look at our intermediate Microsoft Excel courses.

Dates and times often require converting. For example, when performing add or subtract calculations on two dates, Excel will automatically give the result in days unless otherwise specified. It is then necessary to perform another calculation to get the result in the format you require, be that years, months or hours etc. Rather than manually performing that calculation and risk entering an incorrect formula, the convert function will do this automatically. This kind of information is commonly required on sales sheets and general data sheets for reporting purposes.

For example, you may be asked analysis questions such as how long an order took to complete, or how long a sale has taken from start to finish and other reporting information. Presenting those figures in days may not be the best option.

For example, I had a sheet with start and end dates and needed to find the duration between them in hours. This was for a call centre log and was requested because management wanted to know how long each call took. Since the average call time was forty six minutes, it would have looked very unprofessional to present the data in days rather than hours, but due to the format of the data, the result had come through in days. I used the following convert formula to achieve this:

=CONVERT(G5,"day","hr")

This works perfectly, but convert can be a tricky one because you need to make sure you have the correct unit name and they can be difficult to guess. Luckily a Google search can bring up a list of them and it is worth copying them into a saved spreadsheet if you use convert frequently. For example, in the above formula the day unit is the full word 'day' whereas hours is an abbreviated 'hr' which I found a little confusing at first and it led to several frustrated '#NA' results.

As well as various time conversions, there are many other ways this function can be of use. An example would be weight. It may be that older data has been entered in imperial units rather than metric. This could be for a recipe or for a spreadsheet representing the sales data of a traditional family butcher for example. Convert can be used to convert ounces to grams using the following formula:

=CONVERT(A1,"ozm","g")

This formula can be dragged down to fill a column with the metric values. There are several other weight measurements such as teaspoon (tsp), tablespoon (tbs) and cup (cup). Whatever weight or mass you need to adjust, this can be easily done with the convert function.

As well as weight conversions, there are several length conversions which could be useful for some businesses that have imperial data on their sheets. In the UK, many people would still ask for a yard of fabric rather than a metre, and data can be stored on a stocktake spreadsheet easily as both units. This way those people who think in yards can see the yards, those who want metres can see metres. The following formula would achieve this:

=CONVERT(G5,"m","yd")

There are other length measurements available such as inch (in) and foot (ft).

One common conversion would be Celsius to Fahrenheit on weather tables. Both units of measurement are used widely across the globe and it can be a wise decision to make your data as inclusive as possible, especially when it can be done easily and automatically updated by using the convert function. Celsius is a simple 'C' and Fahrenheit is 'F' so it really couldn't be simpler to use convert in this way.

Convert does cover a range of scientific units such as joules, watts and more. When you have data in values which need modernising such as imperial to metric then it is wise to use a convert function in one column, even if you leave the imperial column where it is. If you have a huge range of data and need to know a different kind of measurement for it, why should you bother researching the calculation values yourself when Excel has this amazingly simple and comprehensive formula to complete the task for you?