Understanding Time

Quick reference

• 00:05 In this video we're gonna look at understanding time in Excel.
• 00:08 And if you recall from looking at dates, dates are basically a numeric mask on top
• 00:12 of a number that indicates the number of days since an inception date for Excel.
• 00:17 And as it happens, times are the same thing.
• 00:20 They're actually represented as fractions of days.
• 00:23 And when you put in a base time,
• 00:25 it essentially bases it off of the origin date.
• 00:27 So let's take a look at these particular cells here.
• 00:30 You can see the .13 is a numeric value and if I go on say equals and pull it across
• 00:35 into a cell that's formatted as a time, you'll notice that it comes up as 3:00 AM.
• 00:41 And remember, if times are fractions of days,
• 00:44 a quarter of a day should come back as 6:00 AM.
• 00:48 And if we go and we roll this all the way down, you can see that everyone of these
• 00:53 numeric values is gonna come in as a specific representation of a time.
• 00:57 And this is where the challenge actually comes in with playing around with
• 01:00 these things.
• 01:01 You need to remember this particular component.
• 01:03 So, let's say that I have a value here of 6, well,
• 01:06 I wanna represent it as 6 hours but I wanna get it back into a proper format.
• 01:11 So to do that, what I'm gonna do is I'm gonna say equal 6 and
• 01:15 I actually to divide this by 24.
• 01:17 And at that point, it would come back and say that 6 hours is 6:00 AM.
• 01:22 And if I roll this down now, you'll see that 15 hours or
• 01:26 if you wanna think about it military time is 3 o'clock in the afternoon,
• 01:29 30 hours is 6:00 AM the next day, 48 is gonna be 12:00 AM midnight,
• 01:34 a couple of days away, and 60 is gonna take me to noon, a couple of days away.
• 01:39 Well what if I wanna convert these values into minutes?
• 01:42 Let's say that I had 6 minutes, so I wanna see a time of 12:06 AM.
• 01:47 Basically the way that we do this is we still need to take our value,
• 01:52 divide it by 24 but then we also need to divide it by 60.
• 01:56 And at that point, it comes back and
• 01:58 converts our 6 from a whole number into 6 minutes.
• 02:01 It puts it at 12:06 AM.
• 02:04 And you'll see now, we have 12:15 AM, 12:30,
• 02:07 12:48 and 60 minutes of course becomes an hour, we're now at 1 AM.
• 02:12 If we want to get to seconds, as you'd expect, we're going to do the same thing,
• 02:17 60 divided by 24 for days, divided by 60 for minutes,
• 02:22 dived by 60 for seconds and we're now to 12:00 AM into 6 seconds.
• 02:27 And we can roll those all the way up and notice that after seconds,
• 02:32 we get to 12:01 AM.
• 02:33 So the trick with this whole thing here is it's all about converting our whole
• 02:38 numbers in fractional values to format them as the number of minutes,
• 02:42 since our origin time.
• 02:44 This tends to mess people up a little bit though,
• 02:47 when they're working with time sheets.
• 02:49 Because when you look at these particular values, 8:30 AM and 5:00 PM,
• 02:53 what your temptation might be in order to extract hours, is just simply go and
• 02:57 say well, no problem.
• 02:59 5:00 PM- 8:30 AM, obviously, should give us the number of hours,
• 03:05 except that it doesn't, it gives us 0.35, why?
• 03:09 Well if I go back and reformat these two values for a second, as numbers,
• 03:13 you can see that the true values in the cell are actually 0.71 and 0.35.
• 03:18 So, when I go and subtract one from the other,
• 03:21 rounding aside, it comes out with 0.35.
• 03:24 So it's actually working exactly as it's supposed to.
• 03:27 Now I could format this is a time.
• 03:29 But then if I try and feed it to multiply that by my pay rate,
• 03:33 I'm gonna get 0.35 times the hourly rate, which isn't exactly what I want
• 03:37 because Walter obviously worked a lot more than 0.35 hours during this day.
• 03:41 So I need to try and figure out how I can actually take this value and
• 03:45 get it back up to a whole number, so
• 03:47 I can multiply it by the pay rate to get the correct amount on his paycheck.
• 03:52 So, to do that, we're gonna work the opposite way we did before.
• 03:55 We'll take H7- G7 and wrap it in parenthesis.
• 03:59 Because at this point, we now get a value that comes back to 0.35,
• 04:03 the same as we did but it's a mathematical operation, of course.
• 04:07 I need to convert this into hours.
• 04:10 So I'm gonna multiply those two things by 24.
• 04:14 And now, I'm gonna get to 8.5 hours and at this point, it starts to make some sense.
• 04:19 So if Walter's getting paid $12 an hour, I can now multiply the 8.5 * 12 and
• 04:23 actually give him the right value on the paycheck,
• 04:26 not give him a pittance of what he should be earning.
• 04:29 So the trick with time cards, the trick with working with Excel is to remember
• 04:34 that you're going to need to either multiply or divide by 24, and then 60,
• 04:39 and then 60, depending on whether you're trying to move between seconds and
• 04:44 whole values or whether it's hours or minutes

Lesson notes are only available for subscribers.