Covert Function in Excel: How? What? Why?

This article is intended for those wishing to learn more about Microsoft Excel and for those using Microsoft Excel Version 2010.

So you are dabbling in Microsoft Excel and you’ve been hearing about the convert function available in Excel. Well, first off, what’s the convert function? The convert function is used to change data from one unit to a different type in Excel. It’s most commonly used for date conversions, and surprisingly, it’s one of the most confused and underutilized functions in Excel.

The convert function is at its core an engineering function. You can find it on the formulas section of the ribbon as follows; More Functions>engineering>convert.

So, what requires converting exactly? More often than not, dates & times require converting. Let’s say you are performing an add or subtract calculation on two dates, Excel will automatically give the result in days unless you tell it otherwise. It’s then necessary to perform an entirely new calculation to get the result in the format you want. Kind of annoying right? Instead of manually doing that calculation and potentially putting yourself in harm’s way of an incorrect formula, the convert function lets you do this automatically. How nice right? Information like this commonly required on sales sheets and general data sheets for the purpose of reporting.

For example, what if at your job you were asked to analyze some questions on how long it took for your new hires to complete their training manual, or how long it took them to finish their entry exam. Presenting those figures in days wouldn’t be the best option.

With the time it takes new hires to complete the training manual, I needed to know this because our training manager wanted to know how long each trainer took. Since the average training manual was 60 minutes, it would have been looked at rather unprofessionally 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 worked really well, but convert can be kind of confusing because you have to be sure that you have the right unit name and they can be rather hard to determine. Luckily with a quick internet search on Google, you can find a list of them and it’s worth copying onto a saved spreadsheet for efficient conversion.  Another example is if the above formula, if the day unit is the full word “day” whereas hours is an abbreviated “hr” which I think is a little weird, and it led to several upsetting “#NA” results.

All in all, I believe the convert function of Microsoft Excel to be an important one to understand, and a valuable tool to have in your knowledge base, especially if dealing imperial data on your sheets, or dealing with conversions such as Celsius to Fahrenheit or other similar scientific units like watts, joules, and more. When you have a large range of data and need to know different kind of measurements for it, why would you waste your time researching the calculations to figure it out yourself when Excel is quick, easy and has the comprehensive knowledge to do it for you?

If you enjoyed this article are looking for more helpful article on how to use Microsoft Excel, your may check out this awesome resource library here.