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.

Advanced Formulas & Macros

13. Excel Date Values & Formatting

To kick off the date and time function skill, I first want to talk about something called the date value. And this is the single most important part of working with dates in Excel. So make sure you pay attention to at least this lecture, because the date value is the critical piece that allows Excel to process dates and times. So every date, or actually every moment in time in Excel, has an associated underlying numerical date value. And that date value is how Excel is able to do things like calculate the passage of time or apply statistical functions to dates. Because without those date values, dates would kind of just be text fields, and you wouldn't really be able to do much in terms of manipulating those dates. So Excel is actually really smart about dates, and it recognises most of the dates that are input to Excel and automatically applies and associates the date value. And the way to actually check if your entry was recognised as a date is to go into the cell format and change it to General, and that will actually show you the numerical underlying date value. How these work is that Excel treats January 1, 1900, as the start of time or a date value equal to one, and then counts the days that have passed since then. So January 2, 1900, which is one day later, would have a date value of two. Now, times are also part of date values, and they are captured as decimal values, which start at midnight. So when you have dates in different cells in Excel, even though you are seeing them displayed like this, the real values that Excel sees and uses for calculation are these. Again, January 1, 1900 has a date value of one. Therefore, January 11, 1900 has a date value of 11. May 1920 has a date value of 41, 48. Since that many days have passed since January 1900, and if we had the time on October 2, 2020 at noon, then the date value would be 44,106.5, since noon is the middle of the day. Likewise, October 2, 2020, at 6:00 p.m. Or 1800 would be 44, 100, and 675. Important Note: Different versions and functions within Excel interpret two-digit years differently. So 20 can be either 1920 or 2020. So make sure to always enter four-digit years and your dates to avoid these mistakes. Now that we've established the underlying date values that Excel uses to work with dates and times, Excel also provides several date formatting options, including both standard and completely customizable formats, so that you can display the dates whichever way you'd like. Selecting a cell and pressing CTRL-1 will bring up the Format Selection window. And here you can use standard options in the date category or build your own custom formats using combinations of date and time codes that you can see below. However, any formatting applied will only change the way the date is displayed for the user, but the underlying date value in Excel will always stay the same. Now, the date and time functions that we'll cover as part of this skill are the actual date and time functions, the volatile today and now functions, and the workday and network-based functions, which will all have their own separate lectures. So there's no need to worry about them too much right now. We'll also cover the date serialisation functions, which allow us to extract individual elements from dates like the year, month, day, and weekday, and times like the hour, minute, and second. So with all that in mind, let's jump quickly to Excel to see some examples of date values and date formatting here in Excel. I just want to quickly review what we just saw, starting with the date value. So in the last lecture, we pulled the first review and the last review for our host, in this case, host one. And as you can see by selecting the cells, these are formatted as dates, which means that they must have an underlying date value, and we can actually see these date values if we change the cell format to general. So a date of July 1, 2013, has a date value of 41,456, and a date of July 9, 2020, has an underlying date value of 44,021. And this is actually how Excel knew which were the maximum and minimum dates when we used our MAXIFS and MinIFS functions, since it was just looking for the largest and smallest date values. And even though this is all Excel is ever going to see when looking at these dates, we can actually format them however we want. So let's select both and press CTRL 1 to open our Format Sales dialogue box. So we could use the standard date formats that already exist, like the international format we had before, or we could use the standard US. Format with the month, the day, and the year, which we can see right here, or press Control. Again, we can write our own custom formats. So in this case, I don't know, maybe we want the full month. So four M's followed by a full year So for four years. Press enter, and we get July 2013. July 2020. And really, you can play around with these as much as you want. You can see that there are actually some presets here in the custom as well. But for now, I'm just going to return it to the international format that we had before. And finally, before moving on, I want to highlight the important note I made about two-digit years. So let's say we were going to write the date of January 1, 2020, and we just wrote a 20. So if we press Enter, you'll see that Excel automatically detects that this is 2020. But what if we were using the date function? So this is going to take a year. We're just going to say 20 for a month. We'll do one for January 1 for day.Since we want January 1, close that out, press Enter, and you'll see that in this case, we're getting January 1. Just remember to use four-digit years and you shouldn't have any issues here. And that concludes this lecture and a review of Excel date values. So next, we're finally digging into our date and time functions. So I hope you're ready.

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.