Calculate Total Seconds in Microsoft Excel (cc)

Today's Microsoft tip will work across a variety of versions of Microsoft Excel
and this tip is on calculating total seconds in Excel, which was inspired by a
colleague of mine who asked
how do I calculate total seconds?
and what he meant by that was instead of displaying a timestamp in the hours:
minutes: seconds format
he wanted to instead display the time as total seconds or what we call elapsed
seconds
so his initial question of how do i calculate total seconds really wasn't
about calculating; it was instead about formatting. And what he meant to say was
how do I format time as elapsed seconds
which brings us to a confusing point about Excel for most users
there is a big
difference between content, or how data is stored,
and formatting, or how data is displayed, in Excel
in other words what you see is not always what you get
this is especially true when it comes to Date/Time information
and i'm not going to get into the full explanation just now
but if you'd like to learn more about this topic DannyRocksExcels, another
YouTube video guy, has a very detailed clip on this very topic that you should
check out later
but for now
just know that the bottom line is this
the combination of what you type
and how you format your cells
determines how data is displayed in Excel.
Now let's look at some examples.
So, here I zoomed into a very small, very simple spreadsheet
and I have two columns. I have one column that I want to display the timestamp
in the hours: minutes: seconds format
So I am going to enter a simple timestamp right now; I'm going to enter zero
colon
zero
two, colon
twenty-five
now notice when i pressed enter what it did to the time
moved it over to the right hand side of the cell
and it looks like it should
now when i go back and select the cell i want to draw your attention to that
number format
on the home tab in the number group this little dropdown box now says custom
before it said general and if I click on this little button
in the lower right-hand corner that'll open up
my Format Cells: Number dialog box
and will give me a little bit more information about the formatting that
Excel has automatically applied to the cell
based on the information i typed into it
now i typed the lead zero, the colon, the zero two, the colon, the twenty-five
and Excel goes "Oh, you must must be typing in hours, minutes, and seconds
and it applied this formatting
to that cell
automatically
now watch what happens is gonna go ahead and click ok
and move down to the cell below nominate type something different this time i'm
not gonna type that leads hero i miss kane type the number two colin and
twenty-five when i press enter it looks the same except it doesn't have those
leads arrows
when i select the cell
still says custom up at the top
but when i click on that button to launch the
format cells dialog box
noticed the custom formatting that it is applied
it hasn't applied it as
hours minutes seconds it's applied to this simply hours in minutes
where the two were i wanted to type two minutes and twenty five seconds
excel thinks i meant to type two hours in twenty five minutes
now i can go back and apply
the formatting that i want
but notice what happens to the information when i click ok excelled
didn't read that initial
typing that i did at entering that data in
as minutes in seconds it read it interpret it
as hours and minutes
which is a big problem for my spreadsheet inside really didn't mean to
minutes and twenty five seconds
so that's kind of what i mean by it what you tight is very important
and if you want excel to recognize chorus minutes in seconds or minutes in
seconds unita type that leads hero
now let me know over here to the timestamp second thing and then do the
exact same thing and then its height
he's your own
colin zero two
colin twenty five
and and then the press and tear it does the exact same thing
but now at this time i'm going to change
the formatting i wanted to sway
two minutes and twenty five seconds as total seconds are elapsed seconds
so for that and they need to customize my number format
so going up to that form and dialogue box or i can press the keyboard shortcut
control one and i will
during that format cells dialog box up
right away
what i meant it is tight might own custom format and to get the last
seconds or elapsed anything elapsed hours elapsed minutes
what you do is surround the formatting
with bracket square brackets
so if i want to show just the seconds collapse pani too tight
open square bracket
ass
close square bracket
and it should show you a sample of what that looks like above the your data
actually typed in that cell and what the result is going to be
and now when i click ok
now i am displaying that date of that
two minutes and twenty five seconds as just seconds burlap seconds