Locked lesson.
About this lesson
We examine a timesheet and a payroll calculation to illustrate time as part number, how to format time, and how to calculate gross pay based on time and an hourly rate.
Quick reference
Time Calculations and Format
Understand how Excel deals with time and how to use time in calculations.
When to use
This exercise uses a timesheet to show how to display time and use time in calculations.
Instructions
- One day in Excel = 1 serial number, so 1 day = 24 hours
- 1 Hour is therefore 1/24 (0.041666) day
- 12 Hours are 12/24 or 1/2 (0.5) days
- If 12 hours have passed in a day (12pm), then the day has progressed ½ way (0.5)
- If you use 12 hours in a calculation, Excel uses 0.5 in the calculation, so you will have to adjust for that
- Formatting times change the way time is displayed not “remembered” by Excel
- If you use time in a calculation, let’s say a time calculation on a timesheet adds 30 hours worked. Excel displays 1.25 – which is correct, because 1.25 x 24 = 30. To show these cumulative hours if you want to use it as hours in a calculation, go to Home tab, Number grouping, Format options box, Custom format the number to “[h]:mm” – which will show you the cumulative hours of 30
- Remember, even if formatted, Excel “remembers” the 1.25 even if it shows you 30:00 hours, so to use in a pay calculation of hours x hourly rate = gross pay, adjust your calculation to the displayed hours x 24 x hourly rate to get actual gross pay
- 00:04 So Excel treats days as numbers.
- 00:08 So it only makes sense that Excel treats time,
- 00:11 since it's part of a day, as a number as well.
- 00:15 One hour, for example is one-twenty-fourth of a day.
- 00:20 12 hours is half a day, 36 hours will be one and a half days, you get the idea.
- 00:27 Remember this when you calculate hourly pay.
- 00:31 If a person works 12 hours a day, Excel sees this as half a day.
- 00:37 So if you don't adjust, this may result in half of the person's hourly pay,
- 00:42 instead of 12 times the hourly pay.
- 00:45 We'll take care of that when we get to this gross pay calculation.
- 00:49 But first, let's look at these sample times.
- 00:52 We've got 8 o'clock in the morning, 12 o'clock in the afternoon, and
- 00:56 6 o'clock in the evening.
- 00:59 Let's copy these three dates and paste them right next door.
- 01:03 Now if I reformat these values as a number, watch what happens.
- 01:09 Remember, formatting is a way that Excel displays something to make it
- 01:14 more user-friendly.
- 01:15 It's not necessarily what Excel remembers.
- 01:19 If we take away that fancy time formatting,
- 01:21 these are the values we're left with.
- 01:24 Now, I also want to show you, if I enter time as a fraction,
- 01:28 at 8 in the morning, 8 of 24 hours have passed.
- 01:33 That's 8 out of 24, we get one-third, 0.33.
- 01:38 At 12 o'clock, 12 out of 24 hours have passed, that's a half, 0.5.
- 01:44 And at six o'clock, 18 of the 24 hours in the day have passed,
- 01:49 three quarters, or 0.75.
- 01:52 So we can use this information when we do a timesheet in Excel.
- 01:57 We're going to work this person really hard for the purposes of this example,
- 02:01 because I want to show you the week's time in three days.
- 02:04 Okay, let's say that this poor person starts working at 8
- 02:08 o'clock in the morning and they leave at 6 o'clock at night.
- 02:13 They have worked 18 minus 8, that's 10 hours for the day.
- 02:20 And let's assume that they do the same thing on Monday, Tuesday, and Wednesday.
- 02:26 And Wednesday, let's say that they came in at 8:30 and
- 02:31 then they left at 6:30 or 18:30, ten hours again.
- 02:37 So see how easy that is, time calculations,
- 02:40 it's just one cell minus the other cell and that's it.
- 02:43 Now, the total, this should be easy.
- 02:49 Equals sign, Monday plus Tuesday plus Wednesday.
- 02:53 You can even use the sum function if you want.
- 02:55 Add it up, and you expect a 30 for 30 hours, right?
- 02:59 But what do you get, 1.25.
- 03:02 What does that mean?
- 03:04 30 hours is equal to 1.25 days in Excel.
- 03:10 And while that is technically correct,
- 03:13 we need to see the cumulative hours that this person worked, so how do we do that?
- 03:19 We're going to need to reformat this field.
- 03:22 So let's go up to our number formatting options, and then more number formats.
- 03:28 Go down to custom, I'm going to look for the format where I have hours and minutes.
- 03:38 Hmm, not quite that, that's close.
- 03:41 That option will show a 6, so that doesn't quite work.
- 03:45 I need the option with square brackets.
- 03:47 There's another option that's nicer, there it is.
- 03:51 Square brackets around the h means it's going to cumulatively show you the hours.
- 03:56 And that's going to give us the 30 hours.
- 03:59 So we have 30 hours.
- 04:01 However, what Excel actually remembers is 1.25 days.
- 04:06 So if I have gross pay, which is my $30 times the number of hours,
- 04:11 if I just multiply the 30 by the 1.25,
- 04:14 this poor worker is only going to get 37.50 for their time.
- 04:19 Think back to that discussion we had at the beginning of this lesson.
- 04:23 Excel remembers the 30 hours as 1.25 days.
- 04:27 How do we fix this?
- 04:29 Well, in the pay calculation,
- 04:32 just add times 24 to account for the 24 hours in one day.
- 04:37 This will fix the gross pay to the actual owed pay, and
- 04:42 should help avoid any riots from our overworked staff.
- 04:46 You can see they get $900, as they should.
- 04:50 So that is how you do time calculations in Excel.
Lesson notes are only available for subscribers.