Understanding Time

Quick reference

• 00:04 In this video, we're gonna look at understanding how time works in Excel.
• 00:08 Now, time is very similar to dates.
• 00:10 Where dates happen to be a serial number and
• 00:13 then has a numeric mask on top of it to convert it to the number of days since
• 00:17 January 1st, 1900, time is all about fractions of days.
• 00:21 So, if you think about 0.5, that's halfway through a day,
• 00:25 that would actually return noon on, in theory, January 1st, 1900.
• 00:30 If we take a look at all the little number on the left-hand side here,
• 00:34 I'm just gonna go and write a very quick formula to say equals.
• 00:37 And I'm gonna hit Cmd+Enter to push this all the way down through.
• 00:40 So we're just basically saying these values are equal to the ones next to them,
• 00:44 which is fine.
• 00:45 I'm now gonna go and convert this into a Time format, so
• 00:48 we'll just change it to Time.
• 00:50 And you'll see that what we get with 0.13 is 3:00 AM.
• 00:54 Now this is actually 0.125, is what's going on here,
• 00:57 which is half of 0.25, which gives me 6:00 AM.
• 01:01 Why is that?
• 01:02 Well, there's 24 hours in a day and if you were to divide it by four, so
• 01:07 take a quarter of that 24, you would come up with 6, which is 6 in the morning.
• 01:13 0.5 as I mentioned is noon.
• 01:14 0.38 or rather 0.375 is 9 am.
• 01:16 So we can actually get into all these different areas by
• 01:20 looking at the fractional values that we have.
• 01:23 Now what if we wanted to figure out, I have the value 6 in a cell for example,
• 01:26 and I want to convert it into the number of hours in a specific day?
• 01:31 Well, we're gonna do the same thing here.
• 01:33 I'm going to go along, and what I'm gonna do is, I'm gonna say =, and
• 01:37 we'll go with a set that column to absolute.
• 01:41 So that all of these cells,
• 01:43 when I hit Cmd+Enter, are pointing back to this particular area right now.
• 01:48 And I'd like to figure out how to convert 6 into the number of hours in a day so
• 01:53 that when I actually go and convert this to a time format,
• 01:57 it actually shows me 6 o'clock, not noon.
• 02:00 The way I do that is I actually divide the value by 24.
• 02:05 I hit Cmd+Enter to commit that.
• 02:08 So you'll notice that 6 hours is 6 AM.
• 02:12 And if I was to convert this back to a number, you'll see that it comes to 0.25.
• 02:15 So, let's go back to Time again.
• 02:19 15 hours hours would get us to 3:00 PM.
• 02:22 30 hours would get us to 6:00 AM the next day.
• 02:25 48 hours is gonna get us to 12 midnight.
• 02:29 And 60 hours is gonna get us to 12 noon, a couple days later.
• 02:33 What if I wanted to convert these same values into minutes?
• 02:35 I don't want to see 6:00 AM I wanna see 12:06 AM.
• 02:39 Well, in this case, what I'm gonna do is I'm gonna take this this value,
• 02:43 I'm gonna divide up by 24 to get my number into hours, and
• 02:47 I'm gonna divide it by 60 to get it into minutes.
• 02:51 And again, I'll Cmd+Enter to commit this.
• 02:53 So we get some fractional values.
• 02:56 Let's convert those to times and see what happens.
• 03:00 Okay, so any value divided by 24, we'll get it to hours and divide it again by 60,
• 03:04 we'll get it back to the number of minutes.
• 03:07 Likewise, with seconds, we go one level deeper.
• 03:10 So, we'll divide our number by 24 to get it to hours,
• 03:13 by 60 to get it to minutes, by 60 to get it to seconds.
• 03:17 And again I'll commit that formula.
• 03:18 And these are really small numbers, this e- .05, that's okay.
• 03:21 That's the scientific notation.
• 03:24 When I convert it to a time, you'll notice that I am now looking at 12 midnight and
• 03:28 6 seconds.
• 03:30 Now, one of the really common things that you may wanna do is take something
• 03:34 like a timesheet and figure out how many hours an employee has worked.
• 03:38 So, in order to do this we need to recognize that the values that
• 03:42 we see in the cell here are actually fractional numbers to begin with.
• 03:45 And we can see that by applying the number mask.
• 03:48 It is 0.71 and 0.35.
• 03:50 So go back and convert them back into Times, cuz that's important.
• 03:54 And what I am gonna do now is try and
• 03:56 figure out what is the difference between these two.
• 03:58 So I am gonna say equals 5 PM minus minute 8:30 AM.
• 04:03 And when I do that it gives me 8:30 AM, which is really not what I want at all.
• 04:09 If I look at the value in the cell,
• 04:10 it's coming back with 0.35, which might make sense, except that
• 04:14 we need to actually modify this one in order to get it to do what we need.
• 04:19 So I want in numeric value, but you can see this is too small.
• 04:22 What I'm actually looking for is seven and a half hours.
• 04:24 How do I find it?
• 04:26 Let me wrap the brackets around this guy right now because I want my
• 04:29 order of operations to subtract these two things first.
• 04:31 And now what I'm gonna say is rather than that fractional number,
• 04:36 let's multiply it by 24 hours in a day to come back with 8.5 hours.
• 04:40 And if you actually go and count that backwards,
• 04:42 that's exactly what you'll find, the time difference is between those two.

Lesson notes are only available for subscribers.