Anyone working on the data using excel will know the importance of lookup formulas. They are vital for making almost any spreadsheet or dashboard. That is why when my friend John Franco, who maintains Excel-Spreadsheet-Authors.com, wrote to me about his new book Excel lookup toolbox I was truly excited.
What is in this book?
The Excel lookup toolbox has 120 pages of very useful information on writing lookup formulas (VLOOKUP, INDEX, MATCH) and associated excel formulas like SUMPRODUCT, COUNTIF, SUMIF, Array Formulas etc.
The book starts with a detailed overview of the formulas vlookup, index, match, countif, countifs, sumif, sumifs, sumproduct, averageif, averageifs and array formulas.
Then the book moves to describe 8 familiar lookup scenarios,
- Looking up a number/date/text equal to
- Looking up text that begins with/ ends with / contains
- Looking up case-sensitive text
-
Looking up two index columns and other advanced lookup formulas - Looking up the max/min number, newest/oldest date etc.
- Looking up the exact or lower approximate text/number/date
- Looking up the exact or lower/greater approximate text/number/date
- Looking up the amount of a category
For each of these lookup scenarios, John explains how to write lookup formulas, gives a handful of practical examples (with illustrations) and highlights common mistakes and errors.
Read a sample page of the book.
Benefits of using Excel lookup toolbox
Anyone reading this blog for more than a week would appreciate the important role excel formulas play in building better excel worksheets / models / reports / dashboards. I think there are several benefits of really learning excel lookup formulas.
In my opinion, this book will help you,
- become better at understanding the structure of your data(base)
- write smarter and accurate lookup formulas, thus saving time and effort
- not rush to Google or ask in forums on how to fetch the “x” from “y”
- know exactly why an error is occurring when you see a #N/A or something equally annoying.
- learn how to fix errors in lookup formulas and better still, know how to avoid such errors
What you get when you buy the toolbox?
When you purchase the Excel lookup toolbox you get,
- A 120 page e-book on Excel Lookup Formulas (PDF)
- 45 Excel files with detailed examples of lookup formulas
- A fact-sheet detailing various errors that occur when writing lookup formulas and the remedies
How Much is the Excel Lookup Toolbox?
For a limited time John is selling the toolbox for $19.85. Believe me when I say this toolbox is a steal.
My Review & Opinion
John uses his prior experience in system development to give us several real world scenarios and how the lookup formulas can be written for these. I really liked the way the John presented the content. See the sample page above.
I recommend the excel lookup toolbox for anyone working on excel to manage and report data. The book can teach you some really awesome tricks and help you become better at writing lookup formulas. That, my dear reader is the easiest way to impress everyone at work and get things done.
Any questions?
Do you have any questions about this product? Please drop a comment, John has agreed to reply to your queries directly here. If you bought the product, please feel free share your review here because it will help others.
Disclosure
John is a friend of mine. I have reviewed this ebook even before release (you can read my testimonial in the book as well). I get small commission when you buy the excel lookup toolbox. But I am 100% sure you get much more value out of the toolbox than what John or I make from the sale. Go ahead and get the excel lookup toolbox today.
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 !