99.2% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
How To Use The Convert Function In Excel
Thu 24th May 2012
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:
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:
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:
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?
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
Low Carbon Vehicle Partnership
Very good overall, higly recommend the course to anyone who uses excel as there is always something you can learn to improve your performance. Suggestion: Create a scenario from start to finish of the day to tell a story of a business e.g. you are all working for a green grocers with a mean boss and he wants all of this information by the 5pm. Use the course material to get all this info to the boss etc etc. May make the each item on the course that little bit more relatable/transferable to delegates work practices.
BA BI And Reporting
Power BI Modelling, Visualisation and Publishing
No complaints - the pace was just right and Jens' enthusiasm for the topic was very infectious.
As an added bonus I have learned a lot about Excel, an area I have previously avoided due to a lack of proficiency.
All in all, a very good and productive 2 days.
Manager, Credit & Receivables
From Storyboard to Presentation Deck
My suggestions and comments are as follows:
1. I have said so much from the beginning of the training; I saved the best for the last. One of the best things in this training is the training Support for 2years. Bravo! this is the first of its kind.
2. I am also very happy and grateful for the software short cuts known as the 'Cheat Sheet'. These would be very useful and handy for everyday work.
3. Every other thing I have said in the last feedbacks apply to this one; You all rock! I love your professionalism, kindness and compassion, patience etc.