Transcripts
1. 0 - Introduction, And Welcome To The Course: Hello, My name's Alan and on the creator of this course Excel Essentials Level two for intermediate and advanced students. Now, at the moment, I'm assuming that you already have the fundamentals of Excel mastered. That is, You know what a spreadsheet is? You know how to write a basic formula, and you know where everything on the screen is. No need to worry, if not, because we'll go through things step by step in depth anyway. Now, one of the criticisms I hear when I speak to other students, particularly of Excel courses, is that they're taught a bunch of formulas, but they have no context, so they think the formulas are fantastic, but they can't actually see a reason why they would ever want to use them. For that reason, I deliberately structured this course into individual projects. Now you may have a look at the project title and think, You know what? I have no need to work out how to create a relational database, for example. But within each of the sections, there is some powerful techniques that if you just build it from scratch and you follow the course sequentially and complete each of the sections each of the lectures in those sections, you'll see some powerful formulas at work in context on hopefully then you It will be a short leap to be able to say I I see how I can apply this in this project that I'm working on that work all this other project that I'm working on it work. So although you may look at the tiles and think, I don't need that work through it anyway because there are gems hidden in every section. So the aim of this course is to enable you to build powerful solutions using just the standard Excel functions, maybe combined in a way that you wouldn't think to normally. But you can achieve things that people generally don't think that you can achieve with excel. Okay, so thank you again for choosing this course, and I'll see you in the next lecture. Thank you.
2. 1 - Proof Of Concept: Okay, let's get started now. Recovered in level one of this course that one of the most important things that you conduce whenever you begin a new project is the first thing you should do is always rename your worksheet to something more meaningful. And you can see here that I'll rename this worksheet to time sheet and expenses, Master, because that's the name of the project that we're gonna be working on now before you spend hours creating a masterpiece and excel with lots of fancy formatting and tons and tons of data and formulas is proven at the concept that you're trying to achieve on the concept in this project is very simple. What we want to see is can we create a simple data entry screen where we can get a user to enter the band minimum amount of data and take that data and have it populate multiple reports elsewhere in the workbook without having to duplicate all of the work? OK, so we're going to do that very quickly now just so I can see where I'm working. I'm just gonna highlight these cells so I can see where I'm going to drop the formulas we can change it again later. But this is just so we can see where I'm working. So have just selected cell C one. I'm gonna hear equal Satele IQ. So it's a formula. Move over to a one and his enter on. We can see there in the formula Bar that we've now got a formula that say's I am reference in cell A one. Okay, so far, so good. I'm just going to copy that down seven lines. So now I am referencing on this cell, sell a one in this cell cell a two and so on and so forth. So no. When I populate some data into here, hopefully and it does, it populates automatically over here. So I'm just going to use a drag handle and use the auto fill sequence so we can see Yeah, that concept's pretty much proven their weaken type something in here and have it automatically populate over here without having to enter it again. So far, so good. Okay, the next thing we wanna have a look at is, can we do the same in the same she in a different sheet. So what I'm gonna do is add a new sheet to this workbook by clicking the little plus down here. And that's created sheet three. That's unusual. Let me just zoom in so we can see that nice and big older. Now, if you want to be able to see two sheets in the same workbook at the same time, that's entirely possible in Excel. All you're going to do is you're going to come up to the view ribbon just up here. You're gonna hit view to change from the view ribbon and then you're gonna hit new window. Now what you can see here, let me just zoom in again. We can see here is sheet one on behind sheet one. We can see the same workbook again. Now what we're gonna have to do is we're going to come back to the view menu and we're going to hit view side by side on. When we do that, we can see that in this sheets on the left hand side, Review and sheet one and so far in the right hand side were also viewing sheet one. But what we can do, She's click sheet three and there we have two views of the same workbook, so I'm just gonna do exactly the same thing again. But I'm going to do it in B this time. I'm just gonna hit the home button high like that so I can see where I'm working on. I'm gonna hit, equals. And this time I'm gonna flip over to the left hand screen and click a one on they go. We can see that we now have Monday. Just copy the formula down. So whenever I type in here. So if I say apples, Godspell Apple's still can't spell apples, apples and he sent it. We can see that it's being updated here in this this array on the first sheet, we can also see that's been changed over here on sheet three. Okay, let the only thing to notice here is with the formulas. If we're working in the same sheet on, we looked at the formula. We can see it say's equals a one, and that's because it's reference in a one on the same sheet. If we had a look over here, we can see that the formula has changed. It no say's equals sheet. One exclamation point a one on. That's because the way Excel tells which sell a sheet is on. If it's on the same sheet, it doesn't have anything. It just references the cell. So a one over here. But here it says you need to look in the same workbook we were looking for. A different sheet on that sheet is called sheet one. So they go sheet. One exclamation point. A one means look at cell a one on the sheet called sheet one. Okay, so they go near. The concept has proved we can get on with building our data entry screen. So you in the next lecture.
3. 1 - Planning Ahead: again goes, Let's take a quick look at what we're gonna be creating now. It's always useful to know what you're going to be doing ahead of time, so a little bit of planning is going to save you. Alluded time a bit later on. So let's just have a look at this scan of created of the time sheet that we're gonna be using. You can see from this sheet that we have three different bits of data in there. We have Constance those the things that will never change, such as the logo of the company, which I've cleverly made up to be A J consulted. That's me. There's going to be the contact details at the top left hand side. That's going to be constant, So that's never going to change. So all of the Constance weaken just leave in the template, which we're going to create in sheet to shortly now name providing. It's only going to be the one person is going to use. It can also be a constant. We also have the times that we start in the morning that we started finish in the afternoon . Now they're gonna be data entry, things and they're gonna be dynamic. So we're probably gonna want to have those to our data entry screen, cause that could change each time we enter it. Now, the third type of data, we have our calculations. So when we see the total air was worth, we don't need to enter that because we can perform some maths on the start times and calculate what that is automatically. Candidate signature is just going to be a constant. So we can either print the thing and sign it, then scan it and send it off or just type are naming and have that is the signature on the same thing with the client signature. So we've got three types of days that we have constant data which won't change, which we can just build into our template. We have dynamic data, those of the things that we want to enter into our data entry screen on. We have calculations. There are things that we don't need to enter. We could answer it, but what we're trying to do, we save a little bit of time here. So what we want to do is enter the bare minimum amount of information and have everything else calculators automatically. Okay, so let's have a look at how we build those in
4. 1 - Creating Our Data Entry Screen: Okay, let's have a look at building are simple data entry screen there. The first thing we're gonna do is we're going to stick a title in for our worksheet on. We're gonna call it Data and Tree Screen on. And let's just merge in center that Let's have it in green cause greens nice. I like that green. Have it old and increased the font size. If you don't have to do this, take a look. At course one because we recover that fully in course, one on all the different for matins for text and merge in central stuff like that. Okay, so we need a few gaps in here, selects and set some lines on. The first thing we're gonna need is the week ending date service. Just a week and on, and we're gonna be entering that in there. Now. What I like to do is some is to format the cells into a different color for particularly for data entry screens. Because that is an indicator to the user of which sells. It is they should be filling in that we can look a little later. Hey, you can local the other cells, so they can actually feel anything in that you don't want them to. But for now, let's just enter a simple data entry with the Four Matic. So there we go. We want people to enter stuff in there. We need assuming no weeks out some Monday. We need Monday, Tuesday, Wednesday, Thursday for exactly Sunday Onda We condole it. Rest of it because there are only seven days in a week. Now let's insert a little more from the time sheet that were populating. We can have a look at what entries We actually need to tohave the user enter into here. So let's just bring you up on the screen here and we can see what we want to do. So we need a start time in the morning. So we're gonna put something in here. We need to start time in the afternoon. Sorry. We need a finish in the morning. We need a lunch, but we're gonna calculate that. So what we then need is a start time in the afternoon on. We need a finish time in the afternoon. Pretty much everything else we can calculate out. So let's just stick some hellions in there. So we have start on. We have finish on bets. Just copy those. Stick him over there again. If you If you're not sure about the copy and paste in, check out level one of this coarser because we cover all of the basics in there. So what we're gonna do is we're just going Teoh sent to the text over there so we can see that that style. That's finish since the text over there on, we're gonna have this merged and centers will call that working hours. Okay, so they go, we've got a quite Deb. That's a little untidy. So let's just delete that, uh, a small uniform. Okay, so now we have the basics of a data entry screen. So in here, we can see because the cells are in blue. This is where we want the the users to enter the data. We have a weekend in which corresponds to this box. Over here. We have our star times on Monday morning, Tuesday Wednesday, 35% is Sunday, which we're going to populate into here. We have a finish time in the morning, which populates over here. Andare start time in the afternoon and I have finished time in the afternoon. Okay. Oh, I forgot something. We need also insert. We need another one over there. Don't waste. So we need to say am Andi. Let's just copy that. Dump that in their on call that p p And so they go. No, we can see that we have our working hours in this area. Just here. This is the morning. This is the afternoon. This will be our starting. My morning. Finished time. Morning. Start time in the afternoon From this time in the afternoon. Okay, Let's have a look at how we end some data in as time.
5. 1 - (Custom) Formatting Dates & Time: okay, goes now we can have a look at entry in dates on times no. Everyone knows how to enter a date into Excel on s. Today is Thursday the 30th of July. I'm just going to go ahead inside 30 slash seven on when they center, you can see that excels automatically recognize that as a date and chosen its default date formatting options. So it actually saves 30 Dash Jewell now, because we want to use a week ending rather than the than the actual date. Unless today happens to be a Friday and we're choosing our week end as a Friday, what we're gonna have to do is do some calculations to work out what it is now. We could use Excel to do that, or we could just add it up in our head. So if I didn't know that July had 31 days and have to recite the poem, 30 days has September, April, June and November all the rest of 31 all that kind of stuff. But there's a quicker way to do this by changing the format to a custom formatting excel so you can instantly say, actually, 30th of July is a Thursday s. So what we're gonna do is we're going to right click on here on this cell on. We're going to choose format cells. Now. I'm just gonna drag down into shot just there right now. What you can see, the moments have look at date is that we have a variety of different dates available to us so we could see this and it would say, 30th of the seventh. We could say this, and it would say, 30th or 30 July 2015 sonar so forth all the way down. But what we're gonna do is we're gonna allow own custom. So let's just click on the custom, man. We see we have, ah, a bunch of different ways of format in this, and it's very simple. To change it or we're going to do is highlight the type section just here and delete. Now we can see that the sample sales for 2 to 15 which probably doesn't mean much to at the moment. But we're going to cover that in a lecture in this section. Right? So what we want to do to see the if we wanted to see 30 jaw or 30 07 2015 we would type D D , which gives us the date we could type an M and that would say 07 begin to type space and hit y y so that we'd have 37 15 or if we wanted to dash in between we would say D d dash M M dash y Y, and then we can see 30-7 Dash 15 in our sample window. But if we hit three days, we can see that instantly. It goes to Thursday so that we can say Thursday hitter space d d the 30th on. Then we come on to the months now if we hear em, we get one digit. If we eat em em, it will say 07 because July is the seventh month. If we hit 1/3 M, we can see that it will actually change it to say job eso Let's say let's keep it like that . So let's say 30th Thursday, 30th jaw, let's say yeah, yeah, so they go. That's how we build a custom formats. So we say D d d space that will tell us the day of the week. Dee Dee tells us the date on and M shows us the month in a shortened format as a word rather than the number on 15 is the year. So let's just hit. Enter now that's much simpler to do. So no, If it's a Thursday, I can say that. Look, just type 30th of July and that's a Thursday and actually wanted to be a Friday. So it's just add 12 so they go can instantly see that the 31st of July is a Friday because tell the cell tells me that's on. Obviously, today we can work out very quickly every day of the week s. So if we said let's have a look at the 25th of December 2006 or 2016 we can see the Christmas next year falls on Sunday. They know sneezy, so we want the end of the week. So 37th 215 they go. So that's how you do a custom format for your dates now. Time Very, very similar notes. Some people mistakenly entered time as a decimal, so they said 9 30 There was a 9.5 that's nine have nine hours on behalf on our besides, but all times are separated with colon. So if we're going to start our day at nine o'clock, we'll type nine Coolum 00 and hit. Enter. Now, if we wanted to display the seconds to we could also have a look. A custom format for that. So again, we're gonna choose format cells. We're gonna have a look at the customer. We can see here that we have hh h Cole on M m on from our sample. That will give us nine oclock in the morning. If we wanted to add seconds, we could just come up to here. Hit the colon. Hit second seconds hit, Enter. And there you can see that our entry, although we haven't actually changed the entry will be formatted a zero knowing coal on nine hours 000 minutes. Colon and 000 seconds. Okay, so that's how you create a custom format. Let me just go back and choose hours, hours, minutes, minutes because we're not done interested in seconds. Okay, let's let's populate some data. Just so I retain that formatting, I'm just gonna copy those cells over and then change the Times. So Monday we're going to start at nine o'clock on. We'll have lunch at midday. We're gonna have half of their was lunch. So we're going to come back at 12. 30 on. We're gonna finish it. Five cause five is a fantastic time to finish work. I'm just going to copy those down. So there you go. We've managed to custom for matter of times, and we've custom formatted every day. So we have a bunch of data that we can play with. OK. And the next let's we're gonna have a look at Do some simple calculations on time. Okay, So you in the next lecture.
6. 1 - Simple Calculations With Time: Okay, let's start adding in some simple calculations there. The first thing we want to know is how long did we have full inch. So I'm just going to type the head in there off lunch. Andi, subtract in time is is very straightforward. Exercise and excel. So what we're gonna do, is it equals? Tell Excel. It's a formula. We're gonna look at what time we started back in the afternoon, and we're going to subtract what time we finished in the morning and hit. Enter. So they go. You can see very quickly that we had 30 minutes for lunch, and I'm just going to copy that formula down. That's nice and easy. We worked out what we have one we had for lunch, That's where cut how many hours we worked in the morning on and how many hours we worked. I really wish I could type how many hours we worked in the afternoon on bond. What were total there was for the day. Okay. On again, just that straightforward calculations. We're gonna work out what time we finished in the morning. We're going to subtract what time we started in the morning. It ends actually go. We were three hours in the morning. Let's just use the drag handle. Double click, and they can see all of them. A MLS PM Eris is exactly the same, but we're gonna look at what time we finished in the afternoon on. We're going to subtract what time we started in the afternoon. Hit and they go. We worked 4.5 hours to walk. Click to copy that down. It looks like we need to take lunch a little later in the day to split it up. Message on there. We're gonna do the total hours. Now, you may think that this is a simple as subtracting from your finish time. Your start time groups hit Enter. But you can see that that that saves. We actually worked eight hours a day because we had often there will lunch. We actually worked 7.5. So what we need to do is utilized. Am There was a PM hours. So what we're gonna do is say in the morning. We worked three hours on in the afternoon. We worked for 1/2. So we're just gonna add those two together hit ends at Diego. That's a true reflection of the hours that we worked. Okay, so now we can see we have 1/2 hour lunch, which was calculated. We've worked out how many hours we worked in the morning by subtracting our started time from El Fringe time. Same in the afternoon. And then we ended our am errors and PML was together to work out total hours. What? Okay. And the next lecture? We're gonna have a look at some more useful calculations using time. So you in the next lecture.
7. 1 - More (Useful) Calculations With Time: OK, guys. Now we're gonna have a look at how you round time to specific increments of time. Specific blocks of time. Now, in level one of this course recovered fully the three round functions. They were round, round, down and round up. Now we're just gonna have a quick play with the round function here just to see what it does. So I'm just gonna tie round. Just let me just capitalize that so we can see that it's the round function. We're going to do that, will. That will make it a lot easier when you download the file and you can see exactly what they do. There happened to look at the formulas. Okay, so I'm just concerned the round function in here on. I'm gonna look at the total hours because that's what we want to round now. We could argue because we're working in hours and minutes. We probably wanna work to two decimal places, so I'm just gonna hit too close brackets hit and then copy that formula down with the drag handle. Okay, so now we can see. Let me just have it like that for a second just so we can see that That's the so we're going to be referencing. Okay, so here we can see that seven hours and 30 minutes, which is exactly what we worked, has rounded to seven hours and 26 minutes, which is quite peculiar there. There's a specific reason for that which we're gonna come to in the next lecture. When we look at how Excel copes with dates and times on its perhaps know exactly, do you think it would bay? But don't worry. We'll cover that fully in the next lecture. Okay, just so we can get some variety. I'm just going to change the hours that we work in here. So let's certain let's say we worked till a minute past five on that day on 12 minutes past five groups, along with them their and 12 minutes past five. On that day, let's finish at 5 26 They're fine. 31 man on. Let's finish dead on five oclock on Friday, which is a very good thing today. Okay, so we can see if we finish it. 17 01 which is at a minute past five. We've actually worked seven hours and 31 minutes, but the railed functions given us announcer seven hours, 26 minutes. So clearly that's not right. So what we need to do is find another function or another group of functions that will perform the same thing as rounding but actually work with time. They look like those nice folks that Microsoft have already thought this through on They have given us the Emmett Round function. So let's have a look at what our payable hours would be yourselves type able analyst Gulf and enter to force a new line on. We're gonna be working hours a minutes. Just so it's nice. Implant Bolton enter on were no looking at em. Round they go. Let's just tidy that up. Well, pretty up the format in, um, a little later on. But for now, let's just let's just hope in the m around function again because it's, ah, formula. We're gonna type equals we're going to tell you I m around because of highlighted that so we don't pick the wrong cell. We're gonna be looking at our total hours. We're gonna hit comma. We can see here that we've got a clue as to what we need to tighten next. Just that says multiple. Now the way you express the multiple when you use the M around is you hit the quotes. We want to work in hours. Colon. On this time we want to work in 15 minute increments. So we've said no air was colon on 15 minutes. Then we close our quotes closer brackets on hit, enter. Don't worry about the way that so that results is displayed just at the moment. That's because we have a general formatting on just up here. So what we're gonna do is we're gonna copy the formatting from this cell by hitting the former painter and just paint over. So there you go. You can see that the M Round has rounded seven hours and 31 minutes to seven hours and 30 minutes. Fantastic. That's exactly what we wanted it to dough on. We can see that all of these answers are in multiples off 15 minutes. So there, in 15 minute blocks now that's fantastic. But when we work seven hours and 42 minutes on Tuesday here, Andi, it's highly unlikely, certainly in the UK that you're going to get paid for seven hours and 45 at their Generally , employees will only pay you for 4 15 minute blocks that you worked. So Ram is a very useful later rounding to the nearest increments of 15 minutes or whatever minutes you set just up here So you can have this to anything. You can have it in blocks of one minute to minute three. Anything you want it all. So because we have around function and around, down, on, around, up, we have also the same correspondent. Things similar to em round so em around is the equivalent of round on their The next function that we're going to look at is the floor. So I'm just gonna company that sells over there. But I'm gonna change the bottom word to floor. So that's nice and clear what it is that this particular column is gonna work with that inward. Well, uh, that's better. Okay, so here we're gonna use the same formula. Almost. We're gonna type equals floor. And it's exactly the same for Mattis before. So we're gonna look at the total hours hit comma, open the quotes 00 because we're working in whole Alice on. We're gonna look at 15 minute blocks, so enter and again. Don't worry that we've got a peculiar formatting on there. We're just gonna copy the formatting from here. Paste it over. Okay, let's just narrow that up a little bit. Now we get. So now we can see using the floor function that was still moving in 15 minute blocks because all of these results are in 15 minute increments. So we have 30 minutes and 30 minutes and 45 minutes in the whole hours and 30 minutes again . But this time we can see that seven hours and 31 minutes has been rounded down to the floor of seven hours and 30 minutes, which is what we'd actually get paid for, saying with seven hours and 42 even though we were nearly there, passed that mark and almost got to the seven and 3/4 hours will still only get paid for seven hours and 30 minutes. Similarly, with seven hours and 46 minutes, which you concede just here as Randi to seven hours and 45. And even though we've just gone over the eight hours, we're still going to get paid for the eight hours. Okay, so now we've worked out what the equivalent of round is using the em around function on wood worked out. What the equivalent of around down is using the floor function. Now what we're gonna have a look at is the round up equivalent which may not surprise you to know as round down is floor, that round up is ceiling. So let's just use the ceiling function. Eso Let's go. Ask s say, Hey, I l i n g open brackets select our total hours. Type in a increments. Let's work in 15 minutes. Close on. Let's just copy that down. Okay, now, again, we've got the peculiar former thinks it's just formatted. Generally, let's change the format so they know we have a very kind employer. And if we work one minute over 7.5 hours, they're gonna pay us for seven hours and 45 minutes and so on and so forth all the way down . OK, so that's Hey, you convert time into units of time switches 15 minute blocks. Obviously, if you want to work in 10 minute blocks rather than token 15 just here, just change its 10 or 25 or whenever it is that you want to work in. So they go. That's how you ran the time. In a more useful way. In the next length, you were gonna have a quick look at how Excel actually deals with time on dates on. It may not be, as you think, OK on to the next lecture.
8. 1 - It's About Time (And Dates): Okay, guys, we're just going to step out from the normal laters for a second just to have a look at how Excel deals with dates and times. Now, set this spreadsheet up, which you condemn load from the resource is section on. All I'm gonna do is have the exactly the same date entered in here. I'm just gonna reference this cell in all of these cells on then reference this. So in this cell in this cell now, I've already formatted all these cells up here with the formats that you can see over here . So this one we can seize 25 dash December, which is formatted as dd dash m m. So, if I were to reference that cell, just know you can see that if we formatted the date as just d d, we would get 25 because 25th is the day of the month. So if I reference to sell above it, you can see by using the format d d d. We get some the abbreviated day for suddenly. If I formatted it as four days, you can see that we get the name in full surround insane some. It's a Sunday, and similarly, when we apply the format M m m, we get the abbreviated day for the month on. Then when I format is as m M. M m. Four day four, EMS, we get beat months name. We get the month name in fault. Okay, Now, similarly, we conform at it is why? Why on we can see that we get 2000 we can see that we get 16 which is the abbreviated year for 2016. Or similarly. We can have 2016 my type in four wise in the format in just so you clearheaded that works. Let's just choose the format cells up. She can see there that we've entered a custom formats of four wives that so you change the formatted now in this cell, it's formatted as four days, so we'll get the full day name de de. So we get the two digits of the monthly day number four EMS, so we'll get the full month name and four wise. So we get the full year name so they can see that the 25th of December 2016 is actually some day 25 December 2016. Now is the interesting thing. This cell is formatted as a number with no decimal places so willing it's enter there. You can see that we get the result of 4002. Sorry, 42,000 729. You may be wondering what 42,729 has to do with Christmas Day 2016. Well, that's because when Excel deals with dates, it counts on each day after the turn of the century 1900. So 42,729 is heavily. Days have elapsed since the turn of the century in the ninth in 1900. So if it just changed that there, too the first of January 1900 you'll see that we get one because that's the first day of the last century. Is that last century? I think it's the last century. Eso Consequently, if we went to the several 25th of December 1900 you can see that we have 360 days since the turn of the century. So they go, you can see that That's the way the Excel uses dates. So if we put today's date in, which is the fourth of the 8th 2016 We're now 42,586 days from the turn of the first century. That sorry, the turn of the last century first centric, OK name similarly at below are formatted this cell to a number of game, but this time to five decimal places. So it's just reference that soul there. We can still see that we have 42,000 586 but we have a bunch of decimals after its or a bunch of zeros after it on. That's because what we're actually looking at in this cell just here. If let me let me just have one more in there. Let's just say day they cash move off. This year it was Taylor's minutes, minutes on second group's second seconds. So let me just copy that format in their copy on the plate, this self using the form that sells custom and hopefully we can type it now. Okay, so now we just need to reference that self. Okay, so now we can see that we have the fourth of the 8th 16 0000 on. That's because when you enter a date into excel by default, it knows it to be midnight on that particular day so far to come in here and hit a space and put 09 30 00 you can see that our number has actually changed to 42,508. 6.39583 That may straight strike you was out until we have a look at her Excel actually deals with time. Okay, so over here, I'm just going to be reference in this cell, this e five cell, and this time I'm gonna have a look at it in five decimal places. So this particular cell here has been formatted to a number format with five decimal places . So let's just go ahead and reference that cell and we can see that that 1700 hours five oclock in the afternoon is where talented 0.70833 Now the reason for that is that Excel deals with time as fractions of days. So if you have to change the time here, it's a midnight. You can see that we have a zero fraction of a day. If I want to change it to mid day, you can see that it actually deals with that as 0.5 with lows is a does occur in so half a name. Similarly, if we were to type 6 a.m. we can see that that's course of a day. So it 0.25 Oops, too far. And if I went to six o'clock in the afternoon, you can see that we have 3/4 of the day s o. It will work that out to lose and lose the decimal places, depending on which time you entered. And also it will go to the detail of having seconds calculated. So if we said 07 a.m. at 40 minutes past on 55 seconds, we can see that that is 0.30 to 03 off a day of a whole day. Now the interesting thing Let me just have a look here. Let's just check This is chance at like six am for a second, we can see that we have quarter of a day at 6 a.m. Because course of the day has gone by six oclock in the morning. But if we actually have a look at that formatted as a date, so this. So here's for matters. Dee dee dee dee Dash or space DD dash Mm mm. And on and on. And why, Why, Why? Why? So we have a Saturday, the zero with of January 1900 at six o'clock in the morning. And that's because when Excel deals with time, not dates, just time, it actually assumes that all times on the zero with of January in the turn of the century 1900. So all of your times will be dated on a date that doesn't actually exist, which is 00 January 1900 s. Oh, there's a curious fact for you. So that's the My Excel deals with dates and times. Dates are always whole numbers or call numbers with a remain that if you enter time to. So the dates came some from the turn of the Century 1900 which is why you get a peculiar answer like 42,586 for the Fourth of August, and time is always recorded if you only enter a time as the zero with of January 1900. Okay, there's one. You can thrill your friends with the cocktail parties, and I'll see you in the next lecture.
9. 1 - Adding Time: Okay, guys. In the last lecture, we had a look at how Excel deals with dates and times, and you'll recall that dates are counted on from the first of January 1900 in whole days of which appear before the decimal place on anything that remains after the decimal place will be Cantil is a fraction of the day. You'll also recall that time can't some from a day that didn't actually exist, which was the zero with of January in the year 1900 as gonna be particularly useful when we come to summing time. Okay, so let's quickly now, add in the sum of all these this these worked hours just here. Okay, So if we drank over here, we can see in the quick song that that totals 38 hours and 45 minutes, which sounds about right. And I'm sure it is right. Eso You may think that the easiest way to just some those is just a click on the cell. Billy hit the auto. Some hit the answer on. There you go. And we can copy that answer all the way across. But we get Absolutely, perhaps wouldn't expect. We get the answer of 14 hours and 45 minutes. Whereas we look, if we look at the 1st 2 entries there, we can see already. We've got to seven. So there's are 14 hours on. We've got another hour and 15 minutes sums up so immediately just from the two first cells . We know the answer should be 15 hours, at least. And then we need to. And I ate and ate, and one of the 7.5 hours. Now, the clue is in that zero with of January 1900 So I'm just gonna reference that sell for a second man. But on what I'm gonna do is I'm gonna change the format into Not was It was, like certain. Let's go groups have shown for my dee dee. Uh mm. Boy, Why, boy wide H h s s. So there you go. You can see that now that the results of summing all of these thes results just here, the actual answer would be the first of January 1900 at two oclock in the afternoon. And that's because while excels done is it started at midnight on Saturday, Saturday, the zero with of January counted forward 7.5 hours, then seven hours and 45 minutes from 7 30 and added eight hours onto some of those 28 hours onto the some of those three. Until eventually, it trips over from the zero with of January to the first of January. And by the time you keep out in ALS these up you end up from midnight on the zeroth of January 22 o'clock in the afternoon on the following day. Okay, so that's where you were getting answered. The prints think shouldn't be correct. Now there's a bunch of really complicated and unnecessary steps you can take to actually address this, but there's a much simpler solution. Just go to your results. Come to the format cells option from the right click and just before your H is in your format. Just type of square bracket at the beginning on close it with another square brackets at the end. It's okay on now. You can see that the NC yet is actually 38 hours and 45 minutes, which is exactly what it adds up to in the quick. Some eso. Let's just copy that across. Let's just sense that the formatting. And there you go. That's a you successfully ad time together without tripping over into the foll
