Locked lesson.

Upgrade

About this lesson

Understand how time works in Excel, and how to change from whole numbers into time increments and back again.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2013, 2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Understanding Time.xlsx
14.4 KB Understanding Time - Completed.xlsx
14.7 KB

Quick reference

Topic

Understanding how time works in Excel, and how to convert from whole numbers into time increments and back.

When to use

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:05 In this video, we're going to look at understanding time in Excel.
  • 00:09 And if you recall from looking at dates in Excel, dates are the basically a numeric
  • 00:15 mask on top of a number that indicates the number of days since January 1st, 1900.
  • 00:20 As it happens, times are the same thing.
  • 00:25 Times are actually represented as fraction of days and
  • 00:28 when you put in a base time, it's essentially on January 1st, 1900.
  • 00:33 So let's take a look at these cells here.
  • 00:36 We have some fractional numbers, and
  • 00:38 the cells on my right here are already formatted with a time value.
  • 00:43 So if I were to just go and say equals the cell
  • 00:46 to the left you'll see that .13 returns 3 AM and
  • 00:51 this is because it's a fraction of a day, where 1 would be a whole day.
  • 00:56 As I bring this down 0.25 therefore becomes 6 AM .38 would be 9 AM,
  • 01:02 .5 would be noon, .63 becomes 3 PM, and .88 becomes 9 PM.
  • 01:07 So these are just fractions of the overall day.
  • 01:11 Now, if we were to go back and say we have 6 and
  • 01:15 I want to convert this into an hour in a proper time format, what I could
  • 01:23 do is say =A12/ 24 and that would give me 6 AM.
  • 01:29 And as I go and take this down 15 minutes or sorry, 15 hours,
  • 01:34 would become 3 PM, 30 hours would be 6 AM on the next day, 48 hours would
  • 01:39 be 12 midnight a couple of days later, and 60 hours 12 noon a couple of days later.
  • 01:45 If I wanna do the same thing and convert minutes into time,
  • 01:48 I would say = 60 / 24 / 60.
  • 01:53 That gives me 12:06 AM.
  • 01:58 And you can see now, as I pull these down, that 15 minutes becomes 12:15 AM,
  • 02:03 30 minutes would be 12:30 AM, but remember, these are just values
  • 02:07 that are now being converted into minutes to give me 12:48 AM.
  • 02:10 What if I wanted to convert this value into seconds?
  • 02:15 Well then what I'd do is I'd say equals 6 divided by
  • 02:20 24 hours in the day divided by 60 minutes per hour, divided by 60 seconds.
  • 02:25 So we just kind of compound that and
  • 02:27 make it a little bit deeper a little bit longer.
  • 02:30 And you can see that now we get 12:06 AM.
  • 02:32 Again, remember that 0 is midnight on the first of January 1900.
  • 02:41 So when we get to six divided by 24 divided 60 divided by 60,
  • 02:47 we get to six seconds after midnight on January 1st, 1900.
  • 02:51 Okay.
  • 02:52 So 60 would be 60 seconds, would give us 12:01 AM.
  • 02:57 So this is how we can convert numbers one way.
  • 03:00 Where this tends to bite people is when you actually get
  • 03:03 time cards that look like this.
  • 03:05 And they say, well,
  • 03:06 how do I actually work out what the number of hours elapsed are in between?
  • 03:10 Because what our natural intention would be would be to take 5 PM minus 8:30 and
  • 03:14 say does that work?
  • 03:16 But it comes back with .35.
  • 03:18 And you go well, what's that?
  • 03:20 I mean how does that work?
  • 03:21 Well, remember that these are fractions of a day.
  • 03:24 So midnight on one end is 0, and 11:59PM would be almost 1 from the other side.
  • 03:31 So now what we need to do is we need to convert this back into an actual day.
  • 03:37 The way we do that, we'll go and put brackets around our numbers.
  • 03:41 So C20-B20, that's gonna give us the .35.
  • 03:46 We'll now multiply that by 24 hours.
  • 03:50 This would return an 8.50 hour day.
  • 03:55 One of the things we do want to recognize here is that
  • 03:57 these times as they have been put into the cells are times on January 1st, 1900.
  • 04:01 If I were to grab a value, such as 7.75 and I were to
  • 04:08 go and format that with a time Here we are.
  • 04:14 We can actually look in the formula bar here and see that this is,
  • 04:17 because it was 7.75, this is January 7, 1900 at 6 PM.
  • 04:23 When somebody just puts in a time, when they go with something like 500 PM,
  • 04:29 this one is .71, and when we
  • 04:33 format that with the same format, you'll notice that this is just 5 PM.
  • 04:38 So this is on the very first day, where this one is not.
  • 04:42 Remember, if we go an format these back,
  • 04:45 The difference is that there's a zero at the leading on this one, this is 0.71.
  • 04:49 And that's what happens when somebody puts in a time right into a cell.
  • 04:53 Is that it actually just puts in the time on January 1, 1900.
  • 04:55 So that's something you might wanna just watch out for.

Lesson notes are only available for subscribers.

Gift this course