- Working with time in Excel can be tricky.
Consider the difference between calculating
the number of hours worked in a week
or needing to figure out your arrival time
after adding 45 minutes to your trip.
So, key to calculating time correctly
is using the right time formatting.
Plus, there's options to help you customize the time format.
A common way of formatting time in Excel
is by displaying it as a time in the day.
And you can easily do this by typing
the time that you want to display
followed by AM or PM.
So, let's say you want to show 3 PM as the start time.
You would just type 3 followed by the PM designation.
Just be careful to always insert
a space between the time you enter and AM or PM
because otherwise it will not work.
A different way you can format your time
is by highlighting your times
and then, in the Number section of your Home tab,
selecting the Time formatting.
You'll also find more options to format the time of day
if you select the times you want to format,
right-click and select Format Cells.
Here, if you go to the Time category,
you'll see several options.
Any of the options that show PM next to them,
or are displayed in military time like this 13:30,
can be used to display time of day.
What if your AM or PM time format
gets changed or erased?
So, it's important to know that Excel sees all time the same -
as a fraction of the day.
The values range from zero for 12 AM
to 0.999 for 11:59 PM,
representing a portion of a 24-hour day.
So, for example if you see a 0.50,
that's the same as 50% of a 24-hour day, or noon.
So, let's undo this and let's reformat this again
so that it's not in military time.
What if you instead need to calculate
the hours elapsed between the start time and the end time
to help figure out the total hours?
Well, to do this we'll need different formatting
because we now need to aggregate the time
and a format with AM or PM
is just not going to make sense.
So, to do this we will right-click again
and select Format Cells,
and from the Time formatting
you can select the one that says 37:30:55.
Just FYI, the option above it that says 30:55.2,
that one doesn't display the hours.
The 30 is actually starting with the minutes.
Now, what if you instead want to show
your number of hours elapsed as a regular number
rather than hours, minutes, and seconds like we have here?
Well, you can create your own custom time format.
To do that, right-click again, go into Format Cells,
and this time go into the Custom category.
So, here, you'll already find some
time formats to choose from.
They're the ones here with the H, two M's and two S's,
but you can also build your own.
So, say you want to show
just the total hours that have elapsed.
You can come to the Type field box
and inside brackets type an H and click OK.
So, the brackets are key whenever
you want to calculate elapsed hours and let me show you why.
If you were to type just an H without the brackets,
it's now reflecting as a single digit,
just what would be an hour of a day.
So, we know that the total hours is not two hours,
it was 26.
So, using the brackets ensures that Excel knows
we're aggregating hours and not trying to
display the hour of the day.
Now, if you would like to add elapsed minutes to this,
you would simply append this just by adding
a colon and the minutes.
You can see up here in your Sample box
what that would look like.
Likewise, if you want to also display seconds
just add the colon and two S's.
Now, if you instead want to show your total elapsed time
in minutes and not hours, it would be the two M's
that you would want to include inside the brackets.
For more tutorials like this, click on these links.
Thanks!