Aweber ad END-->   ADJUST IMAGE DATE & TIP TITLE

IMAGE: Excel In Seconds Tips and Tricks from The Excel Addict - Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365  
 TheExcelAddict.com
 
August 8, 2019
 
Hi fellow Excel Addict,
 
As I mentioned previously, for the past few months I haven't been spending much time at my newsletter. I have been working on a remodeling project at a rental property I own an hour's drive from my home. I wasn't expecting that it would take all summer, but unfortunately that's what's often happens with remodeling projects — over over schedule and over budget. However, I'm finally beginning to see the end and hopefully will be finished in another couple of weeks.

Shop Renovations 20190625 Small

This week's tip is in response to a question from one of my readers. I hope it will help you too.

If you like this week's 'Excel in Seconds' tip, please share it and recommend your colleagues and any other Excel users you know to sign up for my newsletter.

Keep on Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com



Moth Webinar Excel Dashboards Button 1908




Send Email. Connect With Customers. Grow Your Business.

With AWeber, you get all the email marketing tools you need to create and send beautiful and engaging emails. For a behind-the-scenes look at how you can use AWeber, sign up to our Test Drive email series:


 
Missed my last newsletter?

Click Here to View it Online




Having a positive attitude can help us in difficult times.
I hope today's quote will help you have a positive attitude today.

Quote of the Day

"Success seems to be connected with action.
Successful people keep moving.
They make mistakes but don’t quit."

-- Conrad Hilton --

 
If you have a favourite quote, send it to me and I may post it in my newsletter.

THIS WEEK'S 'EXCEL IN SECONDS' TIP

 
Enter/Display Minutes Greater Than 60 And Hours Greater Than 24

If you need to record elapsed times such as race times, you may want to enter just minutes and seconds, not hours. To enter minutes that exceed an hour, there's an important concept you will need to know.

When you enter a time, Excel interprets the number before the first colon as hours, the number after the first colon as minutes and, if there is a second colon and a number (optional), Excel will treat that as seconds.

So, with that in mind, if you enter a value 123:45 in a cell, Excel will interpret that as 123 hours and 45 minutes.

Entering Minutes Greater Than An Hour in Microsoft Excel 2007 2010 2013 2016 2019 365

For entering minutes that exceed an hour, the trick you need to know is to enter the time in the format H:M:S with a zero for the hours. So, in the previous example, you would enter 0:123:45 and Excel will correctly interpret this as 0 hours, 123 minutes and 45 seconds.

Entering Hours Minutes And Seconds in Microsoft Excel 2007 2010 2013 2016 2019 365
Formatting Time to Display Minutes Greater Than 60
The second part of this exercise is to apply the correct number formatting to your cells to display these times properly. To format cells to display the time as minutes and seconds and prevent the minutes from rolling over at 60, use the format [m]:ss

To do this, on the Home tab, click the Number Format dropdown, choose More Number Formats... and click Custom. Then in the Type box, enter [m]:ss. This will show total minutes and prevent displaying hours. With this custom number formatting applied to your cells, you can also total multiple time values and display the total as just minutes and seconds.


Display Elapsed Time Minutes Over An Hour in Microsoft Excel 2007 2010 2013 2016 2019 365

Display Total Hours Greater Than 24
As you may expect, this same trick, using the number format [h]:mm, can be used to display total hours greater than 24.

Display Total Hours Greater Than 24 in Microsoft Excel 2007 2010 2013 2016 2019 365




To share this tip with your friends and
colleagues, choose one of these options...

 
Enter/Display Minutes Greater Than 60 And Hours Greater Than 24


 
Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
 
 
 
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630