1 00:00:01,150 --> 00:00:04,120 Now let's take a look at how Excel processes time. 2 00:00:04,130 --> 00:00:11,060 Time is input as hour followed by colon, then minute, colon and if you have seconds then you can input the seconds 3 00:00:11,480 --> 00:00:17,800 followed by space and then a.m. or p.m. You can also input time as a 24 hour clock. 4 00:00:17,810 --> 00:00:22,640 Select military style. So either 7:00 p.m. or 19:00. 5 00:00:22,640 --> 00:00:27,470 Now just like with dates, in Excel time is stored as a serial number. 6 00:00:27,470 --> 00:00:32,420 This time the serial number represents the proportion of 24 hours. 7 00:00:32,420 --> 00:00:39,100 For example, if you put 12 a.m. This is basically 24:00 and 24 divided by 24 8 00:00:39,140 --> 00:00:40,090 is one. 9 00:00:40,100 --> 00:00:48,170 So the number associated with 12 a.m. is a 1. If you input 6 p.m. This is seen as 18:00 and 10 00:00:48,170 --> 00:00:52,640 18 divided by 24 is seen as 0.75 in the cell. 11 00:00:52,880 --> 00:00:57,360 Let's quickly jump to Excel and do some examples on this. 12 00:00:57,560 --> 00:01:04,260 So first off let's input 9 a.m. So you just input 9 followed by colon, 00 for the minute. 13 00:01:04,280 --> 00:01:05,870 Let's say I have no seconds. 14 00:01:05,870 --> 00:01:08,050 Put a space, a.m. 15 00:01:08,090 --> 00:01:11,970 It is also enough to just put in "A" and press enter. 16 00:01:12,050 --> 00:01:20,530 Now let's input 12:00 p.m. here space and then just P. press enter and let's also input 6:00 pm here 17 00:01:20,660 --> 00:01:28,580 Space P and press enter. If we take a look at the formatting associated with this, we see Excel has automatically 18 00:01:28,580 --> 00:01:31,010 assigned a custom format to this. 19 00:01:31,010 --> 00:01:36,920 That's just right mouse click, go to format cells, or use the shortcut key control+1 to see the type 20 00:01:36,920 --> 00:01:37,850 of format. 21 00:01:37,850 --> 00:01:40,220 That's the one it's applied. 22 00:01:40,220 --> 00:01:43,280 So "h" stands four hours and "m" for minutes. 23 00:01:43,310 --> 00:01:46,430 And if we had seconds that would be "s" here. 24 00:01:46,430 --> 00:01:50,280 Here you can see the different custom formats we have available. 25 00:01:50,390 --> 00:01:52,340 Just going to go okay for now. 26 00:01:52,340 --> 00:01:58,340 Right here in the dropdown we have another time format available for us which is this format. 27 00:01:58,340 --> 00:02:01,100 This one also shows the seconds. 28 00:02:01,280 --> 00:02:08,479 If I take a look at what's behind this number here I'm going to erase the format and select general 29 00:02:08,620 --> 00:02:08,810 right. 30 00:02:08,850 --> 00:02:11,740 That's like applying an eraser to this 31 00:02:12,020 --> 00:02:15,920 and that's the number associated with my time. 32 00:02:15,930 --> 00:02:18,630 Okay so just remember this number for a second 33 00:02:18,760 --> 00:02:24,370 and we're actually going to calculate it. So I'm going to press Control+z to go back. 34 00:02:24,410 --> 00:02:26,510 Now here let's do that calculation ourselves. 35 00:02:26,510 --> 00:02:32,730 So this is 9 a.m. I'm going to do 9 divided by 24 and press enter. 36 00:02:32,870 --> 00:02:33,200 Right. 37 00:02:33,200 --> 00:02:40,860 That's the number actually in the cell. For 12:00 p.m. Let's do 12 divided by 24. 38 00:02:41,180 --> 00:02:42,750 That's the proportion. 39 00:02:42,850 --> 00:02:47,900 And for 6 p.m. we actually have to do 18 divided by 24. 40 00:02:48,020 --> 00:02:55,820 Or we can take 12 pm add 6 to it and divide it by 24. 41 00:02:55,820 --> 00:02:56,060 Right. 42 00:02:56,060 --> 00:02:58,490 So that's the actual number in the cell. 43 00:02:58,490 --> 00:03:04,330 We can see that if I highlight this and apply the general format to this. 44 00:03:04,350 --> 00:03:04,560 Okay. 45 00:03:04,580 --> 00:03:07,630 So let's just press Control+Z to go back. 46 00:03:07,730 --> 00:03:14,940 Let's do a practical example here. So let's say I want to track my hours worked for the week. I input 47 00:03:14,960 --> 00:03:20,930 my start time every time I get to work. I put my end time and then I want to calculate hours worked and 48 00:03:20,930 --> 00:03:25,400 then I want to get a total of the hours I worked in that week. 49 00:03:25,400 --> 00:03:28,880 Can I just do end time minus start time. 50 00:03:28,880 --> 00:03:32,020 Let's try that and see what we get. 51 00:03:32,060 --> 00:03:36,830 9:30 a.m. Well, the 9:30 sounds right 52 00:03:36,860 --> 00:03:43,430 because I've worked nine and a half hours on Monday. I'm not really happy about getting AM in here but 53 00:03:43,490 --> 00:03:45,940 let's just push this down and see what we get. 54 00:03:45,950 --> 00:03:50,270 This could mean I worked eight hours and fifty five minutes, nine hours. 55 00:03:50,270 --> 00:03:53,550 These all look right. Can I sum them up? 56 00:03:53,630 --> 00:04:01,070 Let's use the shortcut key Alt+= to get AutoSum in here and press enter. 9 hours 50. 57 00:04:01,280 --> 00:04:03,890 That's my total for the week? Doesn't look right. 58 00:04:04,100 --> 00:04:12,110 I can't sum my total hours in this way because take a look at this. The actual number associated with this 59 00:04:12,710 --> 00:04:14,410 are these fractions here. 60 00:04:14,560 --> 00:04:20,390 And all I'm doing in the Sum function here is adding these fractions together. If I apply may eraser, 61 00:04:20,839 --> 00:04:23,790 that's the actual number in there. 62 00:04:23,790 --> 00:04:32,210 So the moment I want to add hours worked or grab hours worked from time, I need to keep in mind that 63 00:04:32,210 --> 00:04:41,780 these are all proportions of 24 hours. Which means to get the hours worked here I actually need to multiply 64 00:04:41,780 --> 00:04:44,780 this difference by 24. 65 00:04:45,350 --> 00:04:52,070 Now Excel has given me the wrong format back because I want to see the number of hours worked here so 66 00:04:52,430 --> 00:04:58,910 I don't want to see a representative of time, I want to apply my eraser to it or use a number format. 67 00:04:59,420 --> 00:05:06,640 If I send this down here, I can see my total has updated correctly. So I worked 45.83 hours 68 00:05:06,640 --> 00:05:08,980 in that week. 69 00:05:09,010 --> 00:05:12,810 Now I want to round this up, I don't want to get all these decimals there. 70 00:05:12,910 --> 00:05:15,830 So let's round them up to one decimal place. 71 00:05:16,060 --> 00:05:21,450 And here I can use the RoundUp function. I have my value here that I want to round up. 72 00:05:21,480 --> 00:05:22,620 But let's say, 73 00:05:22,630 --> 00:05:24,720 I didn't have these cells here. 74 00:05:24,730 --> 00:05:27,310 I needed to do the calculation in one go. 75 00:05:27,390 --> 00:05:30,230 I just have to do the calculation I did previously. 76 00:05:30,400 --> 00:05:31,900 Inside the round up formula. 77 00:05:31,930 --> 00:05:35,890 So that's going to be end time minus start time, 78 00:05:35,890 --> 00:05:42,880 multiplied by 24. And I want to round this up to one decimal place, closed bracket, and sent this down. 79 00:05:42,880 --> 00:05:51,800 Here my 8.92 hours worked became 9 and 9.4 became 9.5 80 00:05:51,900 --> 00:05:51,940 . 81 00:05:51,940 --> 00:05:57,370 Now let's just pull this over here and I worked forty six hours in that week. 82 00:05:57,400 --> 00:06:00,040 That's how you can work with time in Excel.