Aweber ad END--> ADJUST IMAGE DATE & TIP TITLE
|
|
|
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? |
|
|
|
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.
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.
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 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
colleagues, choose one of these options...
|
|
|
|
|
|
|
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
|
|