Best Seller!
\$27.49
\$24.99

MO-201: Microsoft Excel Expert (Excel and Excel 2019) Certification Video Training Course

MO-201: Microsoft Excel Expert (Excel and Excel 2019) Certification Video Training Course includes 92 Lectures which proven in-depth knowledge on all key concepts of the exam. Pass your exam easily and learn everything you need with our MO-201: Microsoft Excel Expert (Excel and Excel 2019) Certification Training Video Course.

107 Students Enrolled
92 Lectures
08:22:00 hr
\$27.49
\$24.99

Curriculum for Microsoft Excel Expert MO-201 Certification Video Training Course

MO-201: Microsoft Excel Expert (Excel and Excel 2019) Certification Video Training Course Info:

The Complete Course from ExamCollection industry leading experts to help you prepare and provides the full 360 solution for self prep including MO-201: Microsoft Excel Expert (Excel and Excel 2019) Certification Video Training Course, Practice Test Questions and Answers, Study Guide & Exam Dumps.

### 14. PRO TIP: Converting Text to Dates

This lecture will focus on Appro Tip, which is converting text to dates. As we discussed in the previous lecture, Excel is very smart when working with dates and automatically detects a wide range of date formats and converts them accordingly, assigning them a date value. However, unusual or nontraditional formats may be treated as text if Excel is unable to identify them as dates. And in those cases, use text functions like left, mid, and right to extract the date components from a text string and the date function to convert the result into a proper date value. So let's consider this example. We have shark attack data by date, country, and the activity the victim was realising when attacked. Now, to our eye, the date field contains states formatted as year, month, and day without any separations. But Excel wasn't able to detect this. So really, they are just text strings comprised of a series of numbers. And what we can do is use this protip to convert these to dates. The formula would look like this. So first we have the date function. And the date function has three numerical inputs: the year, the month, and the day, and then returns the corresponding date value. And all the information we need for those inputs is in our text string. We just need to know how to extract it. So by looking at our data, we can see that the year is always the first four characters, or the four leftmost characters. So we can use the left function to extract the first four characters from left to right. The month is the third character after the year. So we can use the mid function starting at the fifth character since the first four of the year and extract two characters from there. And finally, the day is always the last two characters, or the two rightmost characters. So we can use a right function to extract the first two characters from right to left. And once applied, the result would be the following: So, Excel has now identified them as dates, assigned a date value to each, and given them a date format, which is perfect. So now let's jump over to Excel and do an example ourselves. Here in Excel, we'll be working in the Host tab of the course project workbook. And before we get started with ourpro tip, I just want to show you guys how Excel automatically detects states. So I'm just going to write a random date here. Let's say January 1, 2020. As you can see, Xcel automatically knew the date and gave it day formatting. And if we press CTRL 1 and go to the general tab, you'll see that it also applied its date value. In this case, 43.831 Now, I know that in this case it's not too surprising since we typed it in exactly this format. But we can go several ways. We can go up to 2100 and it will still do the same exact thing, which is apply the date value and give it a date format. And you can actually go kind of crazy with it. I mean, we could say something like 1120, and it would still know that we're talking about January 1, 2021. The format it doesn't recognize, though, is the one we just saw in the example before, which is Year Month Day. So now if we were to press CTRL 1, you'd see that the general format is exactly what we typed. So it's not identifying this as a date, and it's not giving it a date value. In fact, if we tried to give it a different date format, it just wouldn't go for it. So what we're going to do now is use our Pro Tip. And as you can see, we have a Date Join column here for our hosts. And if we try to give it a general or date format here, you'll see that these are all the same because Excel hasn't identified this as a date. So we're going to add a new column, call it "New Date," and we're going to use the protip that we just learned. So we'll start with a date function, and as you can see, it's going to return the number that represents the date in the Microsoft Excel Date Time code or Date value. So again, three inputs. First, the year. And as you know, this year is going to be our first with four characters. So we need a left function, which will return the specified number of characters from the start of the text string, which is what we want. So our text is going to be this, and the number of characters we want is going to be four. close our left function, and then comma over to the month. Now, for the month, we want the two characters after our year. So since this is in the middle of our text string, we're going to use the mid function, which returns the characters from the middle of a text string, giving a starting position and a length. So we'll open that up. Again, our text is going to be our date. The start number is going to be five, since we have the first four for the year. And then the fifth is going to be the beginning of the month, and it's two characters in length. Close that. Then a comma over to the day, which is the final two characters, or the two characters from right to left, as you know. So we're going to use a right function, which again returns a specified number of characters from the end of the text string. Open that up. We know that this is going to be our text, and it's going to have two characters. Close that, and we can close our date function. And there we go. So, August 27, 2008 And if we press CTRL 1, we'll see that it has a date value of 39 687. So we know Excel is detecting this as a proper date, and we can give it the formatting we want and move it back to the international format since that's what we've been using. Apply this downward. And there we go. It's so simple but very useful. Pro tip on how to convert dates formatted as text to dates that Excel can recognize.

### 15. The TODAY & NOW Functions

Moving on to the Today and Now functions, These are pretty simple. The today and now functions are used to distinguish between the current date and time. And syntax wise, it doesn't get easier than this, as you just need to write the function itself, open the parentheses, and then close them. Now, an important note on why this is the case. These are volatile functions, meaning that they automatically recalculate with any workbook change. So for these examples, which were taken on October 5, 2020, when writing the Today function, Excel returns October 5, 2020. And when writing the now function, Excel returns October 5, 2020, at 11:00 01:00 A.m.with 11 seconds, which is when this was taken as well. Use the ControlSemicolon shortcut for the date or the Control Shiftsemicolon shortcut for the time if you want to save the current date or time as a hard-coded value that you don't want to constantly update. Now, let's head over to Excel and use these in our host dashboard. Here in Excel, we'll be working in the Host tab of the Course Project Workbook, and we'll be filling in the Jacen Last Review and the Last Update fields. Now, for the last update, what we want is the current date and time, and we know that we can use a now function to do that. So let's write that down. Now, open and close parentheses, press Enter, and there we go. So in my case, we get November 4, 2020 at 10:58 a.m. in 17 seconds. And I say my case because this is my current date and time as I'm doing this. But if you're following along, what you'll get is your current date and time. So don't worry if you see anything different, because you should be. Now, since this is a volatile function, if we update our dashboard, then our last update will change. You get 1058 with 44 seconds, 50 seconds, 52 seconds, 54 seconds, etc., etc. And we can also update this if we go to our Formulas tab and use the Calculate Now option. So as we click it, you'll see that now we get 4 seconds, 6 seconds, 8 seconds, etc. So any workbook change or any calculation that we force into Excel will cause a volatile function to recalculate. Now, let's use the pro tips that we just talked about. Assume we only needed today's date as a hardcoded value. Well, you can use the control semicolon, and we'll get November 4. If we only want today's time as a hardcoded value, we can use the Control shift semicolon, which gives us 10:59 a.m. Now, to prove that this is a hardcoded value and this is a volatile function, we can give it: what is that? 21 seconds. So as we calculate, we see we get 49 seconds, 51 seconds, 53 seconds, 55 seconds, and finally, as this moves to 11:00 a.m., you'll see that this stays at 10:59 a.m. as it will remain indefinitely since this is just a hardcoded time value. So we can delete these. You'll see that this will continue to update. And now we want to worry about our day since the last review field. And what we want here is the amount of time that has passed since the last review, all the way up to the current date. So we know the current date is November 4, 2020. And we also know that dates in Excel have numerical date values, and that each day that passes represents an added date value of one. So therefore, we can simply subtract today's state from the last review date, and we'll just get the days that have passed since the last review. So let's do that. If you only want today's date, we can use the Today function, open and close that, and subtract the last review. Now, we can't really select it right now, but we can arrow up, press Enter, and there we go. So we get 122 days that have passed since July 5, all the way up to November 4, 2020. And again, as we continue to update from July 9, we get 118 days. So there you go. The now and today functions.

### 16. YEAR/MONTH/DAY & HOUR/MINUTE/SECOND

Next up, we have date serialisation functions, and these are the year, month, and date functions, which return the year, month, and date from a specified date, and the hour, minute, and second functions, which return the hour, minute, or second from a specified time. Now, the syntax is identified as "nickel" with just one argument, the serial number, which is a cell containing a date or time. And if we look at this example for October 5, 2020, at 11:17 a.m. with 10 seconds and apply this function, the year returns 2020, the month returns 10, the day returns five, the hour returns eleven, the minute returns 17, and the second returns ten, which we can match to our current date and time field and sell B one. So, all in all, it's pretty straightforward. Now let's jump to Excel and use some of these here in Excel. We're back in our host tab on our course project workbook. And since we've added the new Date column that contains proper dates with date values, we can use this to extract useful information about the date joined. So we're going to add two columns, and what we want is the year the host joined. It appears that I did one more dealer. and the month that the host joined. So, as you can guess, for the year we're going to use the year function, and our serial number or date is going to be this new date. So E-2, close that out, press Enter, and it looks like something funky happened here since it seems like Excel is telling us that the year is 19-5 instead of 2008. But what really happened is that when we added our columns, it kept the date formatting from our new date field. So if we press Control One and go to General, you'll see that the date value here is 2008, which is exactly what we wanted. So we just need to change the format here to general. Look at the year. And now if we apply this down, you'll see that these are all eight, which is perfect, and we start to get 20 09, 20 10. So far, everything appears to be in order, and we can proceed to change the format of General in our month. Now write our month function again. The serial number is going to be our new date. Close that out. Month eight. Perfect. We dial 911 after plying it down. Twelve. Perfect. And that's it. quick overview of Excel state serialisation functions.

### 17. WEEKDAY, WORKDAY & NETWORKDAYS

Next up is the weekday function, which you may recall we used previously in our switch function example. And the weekday function returns a number identifying the day of the week for a specified date. Syntax-wise, it has two arguments. The serial number, which is a self, contains a date or time, and the return type, which is a whole number, determines which value corresponds to each weekday. So the default value is one, and that goes from Sunday being one to Saturday being seven. A return type of 2 goes from Monday one to Sunday seven, and a type 3 goes from Monday zero to Sunday six. There are actually more types, but these are the most common. So let's look at the example we used previously. We have the date, airline flight number, and origin and destination airport for several flights, and we want to know which weekdays these are on. Well, we can use the weekday function. So weekday then our serial number isthe date field, so a two. And I usually like to go with a return type of two, which would be Monday one to Sunday seven. And looking at our results, we have a value of four for the first one. So Monday, Tuesday, Wednesday, and Thursday would be four, and then five, six, and seven for the rest. So Friday, Saturday, and Sunday Now, moving on to the workday and network-based functions The workday function returns the date value of the date before or after a specified number of work days, given a starting date. Now the syntax is as you'd imagine: the start date is a cell containing the start date or time. The days are the number of non-weekend and non-holiday days before or after the start date. So give a positive number if you want days after and a negative number if you want days before. And the optional holidays argument is where you can select a reference to a list of holiday dates to be excluded. The network day function returns the number of work days between two specified dates, and it's very similar in syntax. So first the start date which is also a cellcontaining the start date or time, and then instead ofnumber of days you have the end date which isthe cell containing the end date or time. And you also have the option to add a list of holidays to exclude. So looking at an example, we have the start date and due date for a project, and for that project we'll have a check in 20 workdays after the start to review the progress. Well, we can use the workday function for that. So workdays are our start date, BTW, and we want to obtain the date 20 workdays after. so our days will be 20. And let's say we also want to know the total number of work days we have to complete the project. Well, we can use network dates where our start date is b two and our end date is b three, and the results will be as follows: So the check-in would be on September 14, and the total number of workdays for the project is 61. Now let's head over to Excel and practise these. Here in Excel, we're still in the Host tab of the course project workbook. And so far away from our date spot. We've converted it to a correct-date value using our Date function. And we used a year and month function to extract the year and the month that the host joined. And now we're going to add another column, which we're going to call Weekday Joined. And we'll use the Weekday function to determine which weekday each host joined. So our serial number is going to be the new date, or E 2. And for the return type, as you can see, the default is one. And here are the other two examples we just saw. But like I mentioned, there are many more. Now again, my favourite is two. So for numbers one through seven, Monday through Sunday, you can actually arrow down and press tab to select it. Close our function, and we get a value of three, which means that August 27, 2008 was a Wednesday. Ply that down, and there we go. Just adjust this. And now we can move over to our host dashboard. And so far we have the days since the last review, which we just calculated. But now we also want the workdays since the last review, so we can use the network function. Now our start date is going to be the last review. So cell t ten. And our end date is actually going to be today or the current date. So we'll use it. Today, functions open and close. and we don't have a list of holidays right now, so we can just close this. Press Enter. And even though there have been 118 days from July 9, 2020, to November 2020, there have only been 85 workdays. And we can actually add a holiday here to see how it works. So I think Labor Day was September 7 this year, 2020. So what we can do is press F 2 to edit here, and after the end date, we're going to comment over to our holidays and add Labor Day. Close that. And now instead of 85, we get 84. So that's how that works. We can delete this. And there we go. So we have weekday, we have network days, and, for example's sake, let's also use the workday function. So let's say we want to know what date landed 100 work days after our last review. So what we can do is use the workday function. It's going to be our start date. We want 100 work days later. Close that, and you'll see that we get a number. But don't worry, you may have already noticed this, but this is actually the date value. So we can format this as a date, and we get November 26, 2020, so we can go ahead and delete that. And that's it. Short examples of how to use the weekday, network day, and time functions And we're all almost done with our host dashboard.

Download Free Microsoft Excel Expert MO-201 Practice Test Questions, Microsoft Excel Expert MO-201 Exam Dumps

Microsoft.examlabs.MO-201.v2024-05-17.by.juan.8q.vce 1 30.46 MB
Microsoft.examquestions.MO-201.v2020-02-13.by.blake.20q.vce 3 3.92 MB
3
Size
3.92 MB
Last Comment

* The most recent comment are at the top

Feel Free to Post Your Comments About EamCollection's Microsoft Excel Expert MO-201 Certification Video Training Course which Include Microsoft MO-201 Exam Dumps, Practice Test Questions & Answers.

Similar Microsoft Video Courses

Cloud Fundamentals
124
4.6
1 hr
\$24.99
Excel 2013
126
4.6
11 hrs
Windows 10
97
4.5
6 hrs