 |
|
| 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. 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  |
|
|
|
|
|
|
|
| Missed my last newsletter? | |
Click Here to View it Online |
|
|
|
|
| |
|
|
|
|
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.  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.  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 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.  |
|
|
|
|
To share this tip with your friends and
|
|
|
|
|
 |
|
|
|
|
 |
|
| 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 |