Hello everybody. Welcome back to the dashboard course. In this video, we will learn a function that is used to make our life easy in dashboard for that matter anywhere in the corporate world. The function I'm talking about is text. A text is a formula that can extract any relevant data from a particular field, or value which are numbers or dates. Now let's see how we would use the text function in the current example.
We will be using the text function to extract the date from the given fields. Now in the example that I have here, we have dates from May 6 to 24 November 2015 to 2021. The first problem that we encounter is it may be difficult to understand this particular data or the date To view this date in an easier form, more understandable format, simply select the entire data by using Ctrl Space. Select the whole column, use the shortcut Ctrl Shift hash. That is just above number three on the first numerical line just below the function. So when you do that, automatically, it converts the entire data column into a format that is easy to decipher.
Like now, it's simple enough to understand that it is the sixth of may 15, fifth of April 17 to 24th of November 21. Now, that's just the conversion part. But in a pivot table, when we do any kind of pivot table or reporting, pivot requires the data separately so they can be show on the pivot or the framework. To do that we require variables differently. Like if I wish to see the number six, that is the day separately, I may have to use a function. And if I wish to see which week day it is, say Sunday or Monday, I may have to use another formula.
So let's see both of them. To extract the day we can use the formula equals day, bracket open, it requires a serial number. Serial Number is the day. That's to the left, select bracket close, enter, extracted the day from the given date. So it's the sixth. But what if I wanted an actual day like Sunday or Monday, Tuesday, so on and so forth, but I have to use the function text.
Now how does that work? It equals text, open the bracket. First I have to select the date that the value comma, later, we have to enter the format. Now make Excel understand the file format as we do understand. So formatting, customized. We specify the format, dd mm yy.
It requires that format as well. So under double quotation do remember, it has to be under double quotations. Put in the format like you can wish to have date, just put in three DS for a shorter version, something like this. Just close the quotation, bracket closed, enter, so you get the relevant answer. If we wish to see the full format, that is Wednesday, simply press f2 to go into the cell and just put in the D four times to the frequency of the D that we can use a single, double, triple or four times where it provides the relevant answer. Now if single D gives me one digit numbers, Double D gives a formatted number one to 209 If I go for the third D, it gives me the shorter version of the day.
And last but not least is the fourth D, that gives us the full form. That is Wednesday. Now, the same is applicable for the month, and the same goes for the year as well. Let's try it for the month. It's simple equals two. Now if I want the answer in a number, I can go with a month formula.
Select the serial number that is a date, bracket closed, enter, and that gives me the month numbers. But do remember if you're using the text formula, the answers are not a number, but rather in text. Now as you can see the alignment is to the left. But the day is to the right because that by default in Excel, the numbers are on the right, and text is aligned to the left. So if you are using the text function, the answer that it provides his text and not a number. Now keep that in mind.
Now let's Come down to the month. So equals to once again text I select the date, comma, and double quotations this time I wish to have the month so I use triple M is not case sensitive so it does not matter whether the M is capital or small, close the bracket and enter. So I'll just change this over here and convert this to 3ds. Now, that should do it. Last but not least is here. Now here either we can go with text selecting the value comma and double quotation we can go with two y's and that will give me the last two digits that is 15 1718.
Or I can go with four wise that will give me the entire year. That is 2015 2014 2017, so on and so forth. But as we can see the answer is in text. To have the answer in number it's a very simple formula equals two year select the date bracket close, enter. As simple as that, and once done, simply select the whole range which has a formula using the fill handle. And just double click and your formula is applied for all the data.
So I'll just align it to the center just to make the data more presentable. In the next video, we will see how to start with a pivot that is dynamic and understand the expansion of the data itself. Till then keep practicing. I'll see you in the next video. Thank you