Locked lesson.
About this lesson
Understand how time works in Excel, and how to change from whole numbers into time increments and back again.
Exercise files
Download this lesson’s related exercise files.
Understanding Time.xlsx14.4 KB Understanding Time - Completed.xlsx
14.9 KB
Quick reference
Topic
Understanding time.
Description
Understanding how time works in Excel, and how to convert from whole numbers into time increments and back.
Where/when to use the technique
Manipulation of numbers into time formats and back is very common in timesheets.
Instructions
Key Understandings
- Times are fractions of days
- .25 is equivalent to .25 days, or 6 hours
- .50 is equivalent to .50 days, or 12 hours
- Time fractions can be added to a whole date, so 41,633.5 is Noon on Dec 25, 2013
- If a time is entered in a cell without a date, it will default to the time on Jan 1, 1900
Converting whole numbers to time (so they can be formatted as time)
=A1/24 |
Converts the value in A1 into the fractional equivalent that represents hours (24 hours in a day) |
=A1/24/60 |
Converts the value in A 1 into the fractional equivalent that represents minutes (60 minutes in an hour) |
=A1/24/60/60 |
Converts the value in A 1 into the fractional equivalent that represents seconds (60 seconds in a minute) |
Converting time fractions back to whole values (so they can be represented as days/hours/minutes)
=A1*24 |
Converts the fractional time value in A1 into whole hours (24 hours in a day) |
=A1*24*60 |
Converts fractional time value in A1 into whole minutes (60 minutes in an hour) |
=A1*24*60*60 |
Converts fractional time value in A1 into whole seconds (60 seconds in a minute) |
Login to download
- 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.