In the dialog box that appears, in the tab «Number» in the section «Category» you need to select the option «Custom». To highlight to the cell range B1:AY1 and select to the tool: «HOME»-«Cells»-«Format Cells» or just to press CTRL+1. Now you need to copy this formula from the cell C1 in the rest of the column headings in the range D1:AY1. As the result is the 1 – the number of the following month. Next, go to the cell C1 and type the following formula:Īs you can see now the DATE function uses the value from the cell B1 and increases to the month number by 1 in relation to the previous cell. As a result, the DATE function collects all parameters into a single value and the formula returns to the corresponding date. The last argument-is the day number of the month, which is specified in the second argument. -1 – there is 2 months ago from the beginning of the current year (i.e.1 means the first month (January) in the year that is specified in the first argument.The example of the conditions for the second argument with the value: The negative number means that we are interested in what it was a month last time. In the second argument is the month number (-1). In the first argument in the function DATE is the nested formula that always returns the current year to today's date thanks to the functions: YEAR and TODAY. In the picture, the formula returns the period of time passing since the date of writing this article. To do this, in the cell B1 you need to enter the following formula: How does the formula work for automatically generating of the outgoing months? The automatic filling of the cells with the relevant datesĪt first, for the register with numbers of customers we will create to the column headers with green and up to date for months that will automatically display to the periods of time. Presented this decision should automate some work processes and to simplify to the visual data analysis. The main condition for the allocation: if for 3 months the contractor did not make any order, his number is automatically highlighted in red. The user to need only to specify, if the customer have made an order in the current month, in the corresponding cell you should enter the text value of «order». First of all, let's consider to the ready example of the automated register, which is depicted in the picture below. The filling cells with dates automaticallyĪt first, you need to prepare to the structure for filling the register.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |