Understanding Time

Quick reference

• 00:04 Now let's look at understanding how time works in Excel. It's very similar
• 00:08 to dates but there are definitely some idiosyncrasies that we want to be aware of.
• 00:13 Now the first thing we need to know is that time are always fractions of days and what we can see here, I've got a duplicated column, I'm going
• 00:21 to go and format B4 through B7 with a time format you can see what happens.
• 00:27 So here we go, here's time.
• 00:30 And what you can see is that 0.25 equates to 6:00 AM, 0.5 to 12 PM so noon, 0.75 to 6 PM and 1.00 is midnight.
• 00:41 So what ends up actually happening here is that the first, if we divide our day into 24 hours we get six hour increments.
• 00:49 And that's what is actually happening here, 0.25 is the first six hours of the day, 0.5 will take us to the
• 00:54 midpoint of the day, 0.75 just a fractional ratio of what's happening.
• 00:58 Now when we're actually working with these things it's very common especially when we start working with timesheets and whatnot
• 01:05 that we have to convert hours into, or numbers into hours or minutes or seconds
• 01:10 and that can kind of throw some users off. So here's how we actually go about doing this. What we're going do is say well if we have
• 01:17 a value let's say it's equal to one so I'm going to say =A11
• 01:22 and I'm going to divide this by 24 and what that should do is it should divide by my value
• 01:28 that I've given by 24 to give me the equivalent of hours in a day and this one will come up to 0.04.
• 01:34 If I go and drag this all the way down
• 01:38 you'll see that 25 hours works into just over a day and 40 hours equals 1.6 days, 10 hours equals 0.42 days.
• 01:46 So that's what's actually happening in those particular cases so if you're ever looking to divide or to try and figure out
• 01:51 well I have a whole number and I want to know what's the Excel equivalent in hours
• 01:57 that's how you do it, just divide it by 24.
• 01:59 For minutes what we can do is we'll say =A11 again.
• 02:04 We are going to divide it by 24 first to get it back to hours and then remember there is 60 minutes in every hour so
• 02:09 we can divide it by 60 and it's going to bring back
• 02:13 some values that are very very small.
• 02:16 Well that's no problem, let's go in here and let's go and change these into a time format and see what actually happens now.
• 02:24 So now you can see that our five
• 02:28 when we actually say how many hours is it, it comes back with 5:00 AM. 10 would come back with 10:00 AM.
• 02:34 Five when we actually say that this is actually involved in minutes is going to come back with 12:05 AM
• 02:39 because everything remember starts off at the beginning or midnight 12.
• 02:44 The 10 becomes 12:10 AM so that works out well. What if we want to go and deal with seconds?
• 02:49 Let's go and do that now as well. So again I'm going to go back to =A11
• 02:54 divided by 24 because there is 24 hours in a day, divided by 60 because there's 60 minutes in
• 02:58 every hour and again divided by 60 to come to seconds
• 03:01 and again it comes up to 0.00 because the number is very small. Let's go flip that into a time format as well.
• 03:09 12:00:01 so my one has now converted into one second. If I drag that down what do we have? We've got
• 03:16 5 seconds, 10 seconds, 25 seconds, and 40 seconds that looks like it's working nicely.
• 03:22 That's going one way that's taking our whole values and going into minutes.What happens when we actually have our time and we want to
• 03:29 go back the other direction? This is a really common thing that you see with timesheets.
• 03:32 First thing I'm going to do, I'm going to go format both these cells here as time. Make sure that they are going to go in
• 03:37 correctly. So let's say that we have the starting time in this particular example of
• 03:41 2:35 pm
• 03:44 and it converts nicely adding the seconds for me. And now I'm going to say we'll have an ending time of 9:45pm
• 03:53 and that converts for me nicely as well.
• 03:55 So what I really want to do right now is I want to say well I want C19 minus B19
• 04:02 and that will give me 7:10 am. Well that's not really very helpful, I want to know how many hours is that.
• 04:07 So if I go and throw a comma style on it it says 0.30
• 04:12 It's obviously not 0.30 hours, remember time is based on a fraction of a day, so whole days so what we need to do is we need to take this
• 04:21 add our brackets around it and say let's give us this and then we're going to multiply that, it's 0.30 days is
• 04:27 what it turned out to. When I hit enter it comes back its 7.17 hours.
• 04:33 So there you go. That's how we work with time inside Excel both
• 04:36 taking a whole number and turning it into its fractional components to come up with an actual time and also how to subtract two times from
• 04:44 each other to come back to what is the equivalent in hours.

Lesson notes are only available for subscribers.