Santa Clara Chronicles [personal]

Share

Facebook
Twitter
LinkedIn

Note: This is a not an Excel tips post. It is a diary of one of the most awesome conferences I have ever attended.

I just finished attending PASS Business Analytics conference in Santa Clara (USA) and am now heading back home to India. And it has been one of the most fun, uplifting and educational experiences of my life. I met so many remarkable people in this trip.

Just to name a few, I met Dan Fylstra (one of the pioneers of VisiCalc & founder of Solver), Bob Phillips, Ken Puls, Jordan Goldmeier, Oz Du Soliel, Rick Grantham, Szilvia Juhasz, Zack Baresse, Kevin Jones, Avi Singh, Chris Webb, Rob Collie, Bill Jelen, Scott Senkeresty, Matt Allington, Jon Acampora, Marco Russo & Jen Stirrup.

I also felt fortunate to meet many of Chandoo.org fans, followers, customers & supporters who attended the conference. It was non stop fun for 3 days.

As if meeting all these great people, sharing a conversation, beer, snack, moment or ride (in a cramped backseat with 2 other Excel MVPs) with them was not enough, I also got to attend few of the amazing sessions at PASS BA.

  • I learned CUBE formulas from Bob Phillips
  • Introduction to R from Jen Stirrup
  • Power Query trickery from Chris Webb
  • Charting best practices from Jordan
  • Keynote presentations by Mico & Carlo

I wish I had the time to attend more sessions. But I was busy teaching a few or meeting people.

All in all, in one word, PASS Business Conference has been AWESOME.

Couple of funny & interesting experiences from the conference:

5 MVPs in a car

At the end of day 3 (April 22nd), a bunch of us were sitting at the hotel lobby bar and chatting. When I asked Ken (Excelguru) what they are doing for dinner, Ken said Zack is taking him for dinner. Then Zack looked at me and said, “why don’t you tag along?”

By then we were 4 people – Ken, Zack, Wessex Bob & myself.

We all agreed to head back to rooms, fresh up & meet downstairs in 20 minutes.

When we all came down, Jordan was also at the bar area. So we asked him to join us.

Jordan, Bob & I shared the backseat and lots of laughs all the way to some upscale sea food restaurant in another suburb of San Francisco.

Here is a selfies from backseat of Zack’s car.

selfie-in-car

Bob, Jordan & Chandoo

We meet Kevin Jones there and we all share really amazing food, insightful (often hilarious) conversation. As Ken recently quit his job to be self-employed, we all shared our words of wisdom with him.

But the night is not over yet

We reached the hotel at 9:30. I find Rob, Scott, Matt, few members from Microsoft Excel & Power BI teams all having drinks at the lobby bar. So I joined them for more laughs, conversation & selfies.

Here is a pic with Rob, Scott, Matt & Ken

rob-matt-scott-ken-chandoo

Chandoo, Rob, Matt, Scott & Ken

By the time I head to my room it was 11:30 PM.

Dany’s Recalc or Die stricker

Dany Hoter from Excel team has this cool laptop sticker.

recalc-or-die-dany

Almost all the Excel MVPs at the conference in one epic pic

And here it is:

excel-mvp-group-pic

Zack, Jon, Bob, Ken, Chris, Marco, Gregory
Cat, Oz, Chandoo, Rick & Szilvia

My first impressions of everyone

This is the first time I met so many Excel MVPs & bloggers. Here is the first thought that came to me when I saw them.

  • Ken: He is big!!! and he talks fast
  • Oz: What a hat! and whats with the Sriracha hot sauce?!?
  • Rick: he means business
  • Dan Fylstra: Wow, he is so cool & down to earth
  • Scott: Boy his laughter is really loud
  • Avi: small packet of energy & enthusiasm
  • Bob: funny and awesomely English

Thank you PASS & everyone who showed up

Thanks a lot to the PASS team for inviting me to this conference. I had an awesome time.

Also thanks to everyone from Chandoo.org community who signed up for this event & made it even more awesome. Thank you.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

13 Responses to “Convert fractional Excel time to hours & minutes [Quick tip]”

  1. Debraj Roy says:

    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...

  2. Jason says:

    This doesn't work for me. When applying the custom format of [h]:mm to 7.8 I get 187:12

    Any ideas why?

    • Hui... says:

      @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!

  3. WhoKnows says:

    Hi, sorry to point this out but Column C Header is misspelt 'Hours Palyed'

  4. abhishek malik says:

    good one

  5. Julia says:

    So how do I go the other way and get hours and minutes to fractional time?

    • Chandoo says:

      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.

      • Paula says:

        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)

        • Jack Scarce says:

          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

  6. Srikanth says:

    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

    • Paula says:

      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)

  7. Daniel says:

    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.

  8. Tomer says:

    Thanks very much, extremely useful !

Leave a Reply