Now let's take a look at how Excel processes time.
Time is input as hour followed by colon, then minute, colon and if you have seconds then you can input the seconds
followed by space and then a.m. or p.m. You can also input time as a 24 hour clock.
Select military style. So either 7:00 p.m. or 19:00.
Now just like with dates, in Excel time is stored as a serial number.
This time the serial number represents the proportion of 24 hours.
For example, if you put 12 a.m. This is basically 24:00 and 24 divided by 24
is one.
So the number associated with 12 a.m. is a 1. If you input 6 p.m. This is seen as 18:00 and
18 divided by 24 is seen as 0.75 in the cell.
Let's quickly jump to Excel and do some examples on this.
So first off let's input 9 a.m. So you just input 9 followed by colon, 00 for the minute.
Let's say I have no seconds.
Put a space, a.m.
It is also enough to just put in "A" and press enter.
Now let's input 12:00 p.m. here space and then just P. press enter and let's also input 6:00 pm here
Space P and press enter. If we take a look at the formatting associated with this, we see Excel has automatically
assigned a custom format to this.
That's just right mouse click, go to format cells, or use the shortcut key control+1 to see the type
of format.
That's the one it's applied.
So "h" stands four hours and "m" for minutes.
And if we had seconds that would be "s" here.
Here you can see the different custom formats we have available.
Just going to go okay for now.
Right here in the dropdown we have another time format available for us which is this format.
This one also shows the seconds.
If I take a look at what's behind this number here I'm going to erase the format and select general
right.
That's like applying an eraser to this
and that's the number associated with my time.
Okay so just remember this number for a second
and we're actually going to calculate it. So I'm going to press Control+z to go back.
Now here let's do that calculation ourselves.
So this is 9 a.m. I'm going to do 9 divided by 24 and press enter.
Right.
That's the number actually in the cell. For 12:00 p.m. Let's do 12 divided by 24.
That's the proportion.
And for 6 p.m. we actually have to do 18 divided by 24.
Or we can take 12 pm add 6 to it and divide it by 24.
Right.
So that's the actual number in the cell.
We can see that if I highlight this and apply the general format to this.
Okay.
So let's just press Control+Z to go back.
Let's do a practical example here. So let's say I want to track my hours worked for the week. I input
my start time every time I get to work. I put my end time and then I want to calculate hours worked and
then I want to get a total of the hours I worked in that week.
Can I just do end time minus start time.
Let's try that and see what we get.
9:30 a.m. Well, the 9:30 sounds right
because I've worked nine and a half hours on Monday. I'm not really happy about getting AM in here but
let's just push this down and see what we get.
This could mean I worked eight hours and fifty five minutes, nine hours.
These all look right. Can I sum them up?
Let's use the shortcut key Alt+= to get AutoSum in here and press enter. 9 hours 50.
That's my total for the week? Doesn't look right.
I can't sum my total hours in this way because take a look at this. The actual number associated with this
are these fractions here.
And all I'm doing in the Sum function here is adding these fractions together. If I apply may eraser,
that's the actual number in there.
So the moment I want to add hours worked or grab hours worked from time, I need to keep in mind that
these are all proportions of 24 hours. Which means to get the hours worked here I actually need to multiply
this difference by 24.
Now Excel has given me the wrong format back because I want to see the number of hours worked here so
I don't want to see a representative of time, I want to apply my eraser to it or use a number format.
If I send this down here, I can see my total has updated correctly. So I worked 45.83 hours
in that week.
Now I want to round this up, I don't want to get all these decimals there.
So let's round them up to one decimal place.
And here I can use the RoundUp function. I have my value here that I want to round up.
But let's say,
I didn't have these cells here.
I needed to do the calculation in one go.
I just have to do the calculation I did previously.
Inside the round up formula.
So that's going to be end time minus start time,
multiplied by 24. And I want to round this up to one decimal place, closed bracket, and sent this down.
Here my 8.92 hours worked became 9 and 9.4 became 9.5
.
Now let's just pull this over here and I worked forty six hours in that week.
That's how you can work with time in Excel.