We analysts like to compare. If you ever want to keep an analyst busy, just give her 2-3 options. She won’t return to your desk until the cows come home. My wife uses this trick all the time. Picture this:
[In late 2013]
Me: I want to buy a new phone
She: Do you want Nexus 5 or Galaxy S5 or iPhone 5s?
Its late 2014 and I am not done comparing.
So today, let’s talk about an interesting comparison scenario.
Comparing by letter or word
Imagine you are looking at 2 lists like this and you want to know where items differ. Not which items, but where.
That means, you want to know which letters or words in each line are different.
VBA to the rescue
Unfortunately, none of the standard features of Excel (formulas, conditional formatting, pivot tables etc.) can help us with this situation. But we don’t have to give up. We can use a simple VBA macro to instantly compare 2 lists and highlight mismatched letters or words.
[Related: How to compare 2 lists in Excel, a quick round up of techniques]
A quick demo of our comparison macro:
How does this macro work?
When you set out to create macros like this, the first step is to define basic algorithm (logical steps in plain English). To compare 2 sets of data, we need to do below:
- For each item in list 1
- Get corresponding item in list 2
- If they don’t match
- For word match
- For each word in first text
- Get corresponding word in second text
- Compare
- If not matched, highlight in red color
- Repeat for other words
- For letter match
- Find the first mismatched letter
- Highlight all the letters from that point in second text
- Repeat for next item in list 1
- Done
Once you write down this logic, we simply go ahead and implement it in VBA code.
The exact workings of the macro are somewhat complex. So I made a video explaining how the code works & what it can do. Please watch it below.
Video explaining the comparison macro
[see this video on our YouTube Channel]
Download Example Workbook
Click here to download the comparison macro workbook. Examine the code to understand how it is constructed. Feel free to extend it to suit your work needs.
Do you compare lists like this?
Every now and then, I end up having a situation where I need to compare by letter or word. I find VBA macro based solution to be perfect for this.
What about you? Do you compare lists? Where do you struggle with such comparisons? How would you use this macro? Please share your thoughts & tips in comments.
Become incomparable, learn VBA
While VBA is pretty powerful & awesome, not many venture beyond the basic recorded macros. You can transform the work, career & skills by learning VBA. It is not at all difficult and anyone can learn it. Start with below links.
- Introduction to VBA & 5 part crash course
- What is a macro and how to get started with VBA
- 40+ Example VBA macros
- Course: Online VBA Classes from Chandoo
13 Responses to “Convert fractional Excel time to hours & minutes [Quick tip]”
Hi Purna..
Again a great tip.. Its a great way to convert Fractional Time..
By the way.. Excel has two great and rarely used formula..
=DOLLARFR(7.8,60) and =DOLLARDE(7.48,60)
basically US Account person uses those to convert some currency denomination.. and we can use it to convert Year(i.e 3.11 Year = 3 year 11 month) and Week(6.5 week = 6 week 5 days), in the same manner...
This doesn't work for me. When applying the custom format of [h]:mm to 7.8 I get 187:12
Any ideas why?
@Jason
7.8 in Excel talk means 7.8 days
=7.8*24
=187.2 Hrs
=187 Hrs 12 Mins
If you follow Chandoo's instructions you will see that he divides the 7.8 by 24 to get it to a fraction of a day
Simple, assuming the fractional time is in cell A1,
Use below steps to convert it to hours & minutes:
1. In the target cell, write =A1/24
2. Select the target cell and press CTRL+1 to format it (you can also right click and select format cells)
3. Select Custom from “Number” tab and enter the code [h]:mm
4. Done!
Hi, sorry to point this out but Column C Header is misspelt 'Hours Palyed'
good one
So how do I go the other way and get hours and minutes to fractional time?
If you have 7.5 in cell A1,
- Use int(A1) to get the hours.
- Use mod(A1,1)*60 to get minutes.
If you have 7:30 (formatted as time) in A1
- Use hours(a1) to get hours
- Use minutes(a1) to get minutes.
I had the same issue. You can solve it by changing the format as described above:
Right click cell > Format Cells > (In Number tab) > Custom > Then enter the code [h]:mm
([hh]:mm and [hhh]:mm are nice too if you want to show leading zeros)
Thanks guys, these are the tips I'm looking for.
...dividing the number of minutes elapsed by the percent change is my task - "int" is the key this time
It doesnt work for greater than 24 hours
It returns 1:30 for 25.5 hours. It should have returned 25:30
Ideally I would right function as
=QUOTIENT(A1,1)&":"&MOD(A1,1)*60
Sorry, replied to wrong comment....
----
I had the same issue. You can solve it by changing the format as described above:
Right click cell > Format Cells > (In Number tab) > Custom > Then enter the code [h]:mm
([hh]:mm and [hhh]:mm are nice too if you want to show leading zeros)
Clever use of MOD here to extract the decimal part of a number. Divide a number containing a decimal by 1 and return the remainder. Humm. Very clever.
Thanks very much, extremely useful !