Hello, everybody. In this video session, I talked about the concept of date the way it is stored in Excel. And why should you know this? Well, if you happen to work with date formulas, or if you happen to clean the data, when the dates are in the wrong format, you must know this prerequisite that we exist towards the date. If you don't, then you will not be able to perform those data cleaning exercises successfully. And at times, you will find difficulty in writing if formula with respect to dates.
So let's see what lies ahead. Well, to start with every valid date in Excel is a number. And what is that number? It's a number which says the number of days away from the cutoff date. Now, it might sound like a jargon, let me explain through this diagram. Let's assume on our timeline, there is a date called 31st.
December at 99. It's the cutoff date. Now if I write a date, second January 1900, and on top of that, I'm going to Go to Home tab, apply this comma separator and notice it turns to two. Now what is this two signifies, it signifies it is two days away from the cutoff date, not including the cutoff date itself. So, for example, if I take 15th of January 19 100 15th of January 1900, and I press enter, as I apply comma style, same manner to this particular date, as expected, it's going to show me 15. Now, typically you would have found this out at times when you write a formula or a date it quickly gets converted to a number.
For example, you may have tried to write a formula which is supposed to give you a result of 31st December 2011. And on your screen eventually you find the answer in this form 40908. Now, what is this 40908 This signifies that it is 40,009 08 days away from the cutoff date which Date 31st December 2011. Now, this is the way Excel calculates the date. If it is a number it will calculate from the cutoff date how many days it needs to add up to each to a certain date from the calendar point. Now since it's a number, I can also validate whether it is a valid date.
How by using a formula called his number. For example, if I write equal to his number, this is a formula that simply asks whether the self contained is a number or not. And it says true. Now why is this useful? At times, you might be worried whether the data that you are looking at on Excel sheet may not be correct date in terms of format. Now, that would create trouble that would create obstacles when you're doing a lot of date based calculation.
So you want to check whether it's a valid date, in which case you may simply write the formula is number and choose the cell Before it now if it's not a date, it's not understood by Excel, it will say false. So I'll talk about these formats in detail a little later. But this is a formula which helps validate the accuracy of a date. Is this a number? If yes, then yes, it is a date.