Understanding Time

Quick reference

• 00:04 Dates and times follow a similar standard in the way that they're built.
• 00:09 And again, in layman's term, it's the amount of time to lapse since January 1st,
• 00:13 1900.
• 00:14 Although you're going to see there's a weird little nuance around this as well.
• 00:17 So if we look at this, we can say, okay, well, let's take a value of 0.13, and
• 00:22 that is a fraction that represents a portion of a day.
• 00:25 So if I come back and say equals Cell A6 and I hit Enter,
• 00:29 you can see that I've already got this cell formatted as a time.
• 00:33 And it returns 3 AM.
• 00:35 Now, it's a little bit hard to visualize 0.13 of a day.
• 00:37 So let's take this down one more.
• 00:39 And we're going to go to 0.25 of a day.
• 00:41 So quarter of the way through the day.
• 00:43 We know the day is 24 hours.
• 00:45 If you divide that by 4, you get to 6.
• 00:47 And therefore, we've got 6 AM.
• 00:50 If I go and roll this up to 0.5, you can see that we get to noon on a given day.
• 00:55 And if I take it up to 0.88, we're at 9:00 PM.
• 00:59 If I go to 1.5, I'm going to get noon on a day as well.
• 01:03 Now, here's the weird thing about this.
• 01:05 Like I said, all dates are based on, in layman's terms,
• 01:08 the time lapse since January 1st, 1900.
• 01:11 And times are as well, except that there's an odd nuance that was built for
• 01:16 compatibility with Lotus 1-2-3, way back in the day.
• 01:20 So I know that this is a date slash time, and if I hit this,
• 01:24 you'll see that it comes back with a day of 0.
• 01:27 And if we go all the way down to here, my 1.5 returns Day 1.
• 01:32 And this is because Lotus 1-2-3 actually messed up on the leap year in January,
• 01:37 1900.
• 01:38 And in order to actually make things work properly when importing spreadsheets,
• 01:42 the Excel team actually has a Day 0 back on January 0, 1900, and
• 01:46 that's where your times start.
• 01:48 So one day in is actually January 1st.
• 01:51 And that all corrects itself by March 1st of 1900.
• 01:55 Now, that's trivial pursuit knowledge.
• 01:57 You're not going to care about that.
• 01:58 The important thing here is to realize that there is a base that you're counting
• 02:03 from, and times are fractions of days.
• 02:05 Now, what does that mean?
• 02:06 Well, what if I have a value of 6, and I want to convert that to be six hours.
• 02:13 What I'm going to do, is I'm going to come over here.
• 02:15 Let me just knock this in with a absolute on Column A.
• 02:19 And what I'm going to do,
• 02:20 is I'm actually going to divide this by the number of hours in a day.
• 02:25 That will return 6 AM, because it's 6 divided by 24 hours.
• 02:30 Gives me the fraction that's required in order to return 6 AM.
• 02:34 And you can actually see that.
• 02:36 If we format as a comma style, it's 0.25 all the way through the day.
• 02:40 So let's go back to time.
• 02:42 And now we can roll this down.
• 02:44 And you can see that we get to 6 AM, 3 PM.
• 02:47 We have 30 units that we've converted here for hours.
• 02:51 This is now going to take us to 6 AM on the next day.
• 02:55 48 will take us to 12 on two days after we actually started.
• 03:00 And 60 is going to give us noon, two and a half days after we start, okay?
• 03:04 So from our base.
• 03:06 What if I want to convert these things now?
• 03:08 So I want to take 6, and I want to convert it into the minute equivalent.
• 03:12 Well, what I'm going to do, is I'm going to take all of these and
• 03:15 copy them over.
• 03:16 But I'm going to go and adjust the formula to, after dividing by 24,
• 03:21 to convert our value into hours.
• 03:23 We're then going to convert it into the number of minutes by dividing that by 60.
• 03:28 I'll hit Ctrl + Enter.
• 03:29 You can now see I have 12:06 AM, 12:15 AM, 12:30 AM,
• 03:35 because my 30 is now 30 minutes per se, and 60 gives me a full hour of 1 AM.
• 03:41 If I then want to make this to become seconds, so I'm looking for 12:00:06.
• 03:47 Let me just drag this over a little bit more.
• 03:49 And once again, I'm going to further divide it by 60.
• 03:54 And there we are, 12 AM and 6 seconds,
• 03:57 12 AM and 15 seconds, all the way up to 12:01 AM.
• 04:01 Now, I don't find it as common to turn values into times as I do
• 04:06 to deal with timesheets.
• 04:08 But that's the business world that I'm in.
• 04:11 This one here for me is quite common.
• 04:13 I have a start time and an end time, and
• 04:15 I want to know how many hours have actually elapsed in between these things.
• 04:19 So let's do it.
• 04:21 We're going to say equals, open parenthesis,
• 04:24 we're going to take our 5 PM minus the 8:30 AM, close the parenthesis.
• 04:30 We'll take that result, that is going to give us 0.35,
• 04:34 which represents the fraction of the day.
• 04:36 So to convert it back to hours that makes sense to us,
• 04:40 we multiply it by 24 instead of dividing it by 24.
• 04:43 And at that point, we get 8.5 hours that Walter has actually worked.
• 04:48 So this is fantastic!
• 04:49 We can actually use this to calculate timesheets and
• 04:53 elapsed times that are actually going on.
• 04:55 The big secret on this, is really understanding that we actually take these
• 04:59 things and divide them by 24, and then by 60, or by 60.
• 05:02 Or multiply them out the other way in order to get those times exactly right,
• 05:06 and convert them into values, or vice versa, as needed.

Lesson notes are only available for subscribers.