In many cases, whether for homework or a business-related activity, we often need to find out how to include months into our excel documents.
This is where common sense kicks in, and you start thinking: “OK, I know what I have to do. All I would have to do is add the number of months that I am asked to get my answer.”
And then you go ahead into doing the math without even understanding if that action might affect your file adversely.
Of course, it affects your file! The natural way of solving this problem is by using the DATE function from Excel, which converts text into a date data type.
How to Create a Formula in Microsoft Excel:
Click on File tab > Options > Advanced option.
Scroll down until you find the General section, copy & paste the following line inside the textbox labelled ‘Date separator’ and click OK: / You can now type 2016/07/26 without quotes and press Enter…
In addition to this, you can also format your dates in different ways that best suit your needs! For example, I chose to display my dates with the month first then the day second.
So in cell A1, I typed “July 26” without the quotes and pressed Enter.
And just like that, it appears as July 26 in my spreadsheet! Now, if you want to display your data in this format instead, where month comes first followed by day, type =TEXT(A1, “mmm dd”) without the quotes.
The TEXT function requires only two arguments – either a cell address or a string of data enclosed within double quotation marks – comma-separated with no spaces between them.
TEXT takes whichever string you inputted through these arguments and converts them into data according to how you define your cells to appear.
For example, =TEXT(A1, “mmmm dd”) would give me July 26 as well.
The only difference is that now if I were to change the format of cell A1 from Date to General, instead of seeing 2016-July-26, it would appear as 7/26/2016, which makes the TEXT function a very helpful one.
The main advantage of using the TEXT over DATE function is how much easier it becomes if you want your dates in different formats or don’t want the year appearing without clearing out the cells and starting over.
Another common date formatting method used by many people would be to display years as yy or yyyy, where four digits are required for year inputted into the cell.
We’ll have =A1-TEXT(A1,”yy”) in cell A1 for this example. This will give us 2016 since that was inputted into cell A1.
You can also use this in conjunction with the TEXT function to produce the data output you want in cells in your spreadsheet.
For example, =TEXT(A1,” yy-mm-dd”) would give me 16-07-26, which I want my dates to look like!
However, if there are times when you don’t have all the information yet for a particular date, then it becomes difficult to format that way when you are not sure what you want yet.
In cases like this, it may appear easier to clear out the data and start instead of formatting the whole thing in a different format you’re not 100% sure on.
If there are times when you don’t have all the information yet for a particular date, then it becomes difficult to format that way when you are not sure what you want yet.
In cases like these, it may appear easier to clear out the data and start instead of formatting everything in a different format that you aren’t 100% sure on yet.
If this is still not an option, I recommend using the TEXT function as it gives me more freedom to choose whatever format best suits my needs depending on what I will be using that date for!
You can even leave dates unformatted if you’re just simply inputting numbers into your spreadsheet! It’s all up to you.
If you add a column to your excel sheet and add the number of months to whatever date is in that column (Date 1/1), it will automatically add to whatever day you put into that cell. I.e., Date 3/1: It would automatically turn this into Date 4/1.
Make sure the month stays at 0 if it’s not chosen yet, or else it will show -1 for September or something like that instead of 11.
Also, make sure the dates are all in order, so you don’t get strange results such as 3/31 being two months away from 5/1 but 6/31 showing up as eight months after 5/1 instead of 7.
Working with Date Function
The DATE function takes two arguments: a number and a text.
Now, if you give it a number that represents the month to add and then a string that is going to be converted into the date, then what would happen is that you will get your answer with no problem at all because Excel understands that it should convert the string into a date for you before adding it to the original date.
It looks something like this:
=DATE(A1+MONTH(B1), B2) where A1 is the ordinary date in cell A1 which contains any given date.
And B2 represents the month to be added with whatever value in cell A1. There was no need to do any complicated math at all.
If you want to add years, then it is the same process except that instead of adding a number representing the month, you will be adding the number of years to add, which would be represented with the “YEARS” variable.
=DATE(A1+YEARS(B1),B2) where A1 is a normal date.
And B2 is a string in cell A1 representing the year to add.
The only disadvantage I see about using the DATE function in Excel is that it might mess up your formatting if you have applied a different format on your cells. It works best, and I think if the cells’ formats are already set to date.
Now, let’s move on to changing a normal date into a date format in a cell.
Then, you will be able to choose from various ways of displaying dates in your spreadsheet.
In cell A1, type “2016” without the quotes and press Enter. Cell A1 displays 2016 because, by default, Excel automatically adjusts your data type according to what you inputted inside that particular cell.
If we want 2016 to appear as 2016/07/26, we need to adjust it so that Excel knows you mean 2016 only.
On the bottom right corner of your screen, click on File tab > Options > Advanced option. Scroll down until you find the General section, copy & paste the following line inside the textbox labeled ‘Date separator’ and click OK: /
Now, go back into cell A1 again. Try typing 2016/07/26 without quotes and press Enter. You should see 2016-July-26 in cell A1 now! Like magic, right?
The date separator you just entered makes Excel read what is inside cells as two different things – year first then month second – separated by a forward slash (/).
So if I didn’t put this in, Excel would read 2016//07/26 as of July/2016, which is wrong. You can go ahead and try it out on your own if you don’t understand this explanation.
Note: If you want to leave the year as is and create months, for example, then place a number sign (#) before the forward-slash (/).
This will tell Excel to use your number first and ignore anything after that sign. =2016#/07/26 where only numbers within cells are read and no text characters. Thus, 2016 appears at cell A1’s end instead of 06.
In addition to that, you can also format your dates in different ways that best suit your needs! For example, I chose to display my dates with the month first then the day second.
So in cell A1, I typed “July 26” without the quotes and pressed Enter. And just like that, it appears as July 26 in my spreadsheet!
Now, if you want to display your data in this format instead, where month comes first followed by day, type =TEXT(A1, “mmm dd”) without the quotes.
The TEXT function requires only two arguments – either a cell address or a string of data enclosed within double quotation marks – comma-separated with no spaces between them.
TEXT takes whichever string you inputted through these arguments and converts them into date according to how you define your cells to appear.
For example, =TEXT(A1,”mmmm dd”) would give me July 26 as well.
The only difference is that now if I were to change the format of cell A1 from Date to General, instead of seeing 2016-July-26, it would appear as 7/26/2016, which makes TEXT function a very helpful one.
The main advantage of using the TEXT over DATE function is how much easier it becomes if you want your dates in different formats or don’t want the year appearing without clearing out the cells and starting over.
Another common date formatting method used by many people would be to display years as yy or yyyy, where four digits are required for year inputted into the cell.
We’ll have =A1-TEXT(A1,”yy”) in cell A1 for this example. This will give us 2016 since that was inputted into cell A1.
You can also use this in conjunction with the TEXT function to produce the data output you want in cells in your spreadsheet.
For example, =TEXT(A1,” yy-mm-dd”) would give me 16-07-26, which I want my dates to look like!
However, if there are times when you don’t have all the information yet for a particular date, then it becomes difficult to format that way when you are not sure what you want yet.
In cases like these, it may appear easier to clear out the data and start instead of formatting the whole thing in a different format you’re not 100% sure on.
Step Guide:
1. Open your template file (Microsoft Excel) and on top, type in the following cells’ format that you need, then press Enter.
You can also copy-paste them if you want instead of typing it manually; cell A1 = Month (the number), cell B1 = Year (four-digit only).
2. After entering the calendar information, highlight all the cells starting with Cell A2 until End, except for your entry column/s, highlighted in blue.
Now click the Date tab at the top left corner of your screen > Sort & Filter group > Click A to Z button. This will sort your data automatically, and you can now see the new date added at the end of your list.
3. In this example, I entered January as the Month number and 2016 as the Year number.
Now, if I want to add December 2017 after January 2016, all I have to do is highlight from Cell B2 until End > Click Data tab > Sort & Filter group > Click A to Z button again, then my new set of spreadsheet looks like this:
4. You can also change how it displays the months before adding a certain year by changing the formatting of your Month Number cells (A1).
For instance, cell A1 shows July 2017, but you want it to look like July 2017 instead of 1. You change the format of cell A1 by highlighting it first then clicking Home tab > Number group > More number formatter.
5. Finally, you can use conditional formatting on your spreadsheet if you want. Highlight your cells B2 until End again > Click Data tab > Sort & Filter group > click Manage Rules button at the very bottom right corner of your screen.
You will get a popup window with several options; create a new rule is selected but we don’t need to do anything there, for now, so make sure all are except Formula Is and Edit Rule is selected.
6. Go to New Rule > Use the formula to determine which cells to format > Next.
7. In this example, I created a rule that looks for any numbers in the cells and formats them as text (for Month Number cell A1, it displays 1; for Year number B1, I entered 4).
You can also create a custom format here, but you need to know how if you want more advanced formatting that includes a combination of characters and patterns.
