In the third session of Chandoo.org podcast, we are going to get BI curious. 😉
Not that kind you silly, We are talking about Business Intelligence, Big Data, Power Pivot & other Power BI family members.
First some good news: We hit #1 on iTunes in our category
That is right. Around 14th of March, we hit #1 in our category of podcasts. Yay!!!
Please take a minute to subscribe to our podcast:
- Chandoo.org Podcast is on iTunes: Click here to subscribe.
- Our show is on Stitcher and Windows Phone Podcasts too: Click here to subscribe on Stitcher. For Windows phone, search your podcasts app for our show.
- As always, you can get latest episodes, show notes & resources from our Podcast page.
What is in this session?
In this session, I am happy to feature Mike Alexander – Microsoft MVP, Author, Blogger & a good friend. Mike talks about how Excel is shaping the BI (Business Intelligence) revolution with advent of Power BI functionality.
- 10% discount on Mike & Bill’s upcoming Power BI Bootcamp in USA
- Free Excel Dashboard Tools add-in
Listen to the podcast to know how to get these.
You will learn,
- Introduction, what Mike is up to these days?
- What is BI, what does it mean to an average Excel analyst?
- What BI capabilities Excel has – brief intro to each of them
- Power Pivot & what it does
- Power Query & why it is important
- Power View & how it works (and where it sucks)
- Power Maps
- How to learn about these new technologies
- Recommended Books
- Websites
- Courses
- Live classes
- Special gift for our listeners
Go ahead and listen to the show.
Podcast: Play in new window | Download
Subscribe: RSS
Links & Resources mentioned in this session:
Mike’s website – Data Pig Technologies
Books mentioned
- Microsoft BI Tools for Excel Analysts by Mike, Jared & Bernard
- 101 Ready to use Excel Macros by Mike Alexander
- Excel Dashboards & Reports by Mike Alexander
- Power Pivot for the Data Analyst by Bill Jelen
- DAX Formulas for Power Pivot by Rob Collie
- Power Pivot Alchemy by Rob Collie & Bill Jelen
- Author profiles of Chris Webb & Mike Alexander
Power Pivot related articles & resources
- Why Excel shines & where it falls compared to other BI tools [PowerPivotPro.com]
- Introduction to Power Pivot & how to use it?
Add-ins mentioned:
- SQL Server Data Mining add-in
- I could not get a link to the segmentation, clustering add-in I used way back in 2006.
Dashboards mentioned:
Courses mentioned:
Mike & Bill’s upcoming Live Class
Transcript of this session:
Click here to download the transcript.
Thanks to Mike
Thanks Mike for joining me and sharing your knowledge, humor and passion with our podcast community. As always, it was fun talking to you. I look forward to featuring you again in our show sometime.
- 10% discount on Mike & Bill’s upcoming Power BI Bootcamp in USA
- Free Excel Dashboard Tools add-in
Listen to the podcast to know how to get these.
If you enjoy the podcast…,
Please do these 2 things. It means a lot me & our community.
- Subscribe to our podcast on iTunes
- Please leave an honest review on iTunes by clicking here.. It will help the show & its rankings immensely. Thank you so much.
Do you use Excel’s BI capabilities?
Do you use Power Pivot et al? How do you like them? Do you feel these tools empower you & your business? Please share your experiences & thoughts in comments.
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 !