This spreadsheet was created at request of a site visitor. It shows how to create a 12-month calendar from just the year number. It's not the finished article, just an example and a method, so you can have a go! I suggest you have a look at the post on using Excel Dates, so that you understand what I've done any how it works. Excel template containing the start point: Perpetual Calendar Workings
Dealing with dates in Excel is easier than you think. For a start, dates are just numbers. The number 1 is 1 Jan 1900, 2 is 2 Jan 1900, and so on - we're now over 40000. (In the help files and function descriptions, they are referred to as 'serial numbers'). That means that you can add and subtract dates, or move along a calendar by adding to/subtracting from a date. Secondly, if you want to know day of the week, month of the year, or even number of working days between two dates, there's a function to do it. Excel [...]
Dates are just numbers in Excel. To calculate the number of days between two dates, just subtract one from the other (you might find Excel automatically formats the answer as a date, in which case you need to change the number format back to 'General' or 'Number'). However, it's also often the case that you need to know the number of whole years or months between two dates. A recent example - has this person turned 17? In this case, you can either write a complicated formula to calculate it, or you can use the Excel function DATEDIF() - read it [...]
Many legacy systems export their data in human-readable form, rather than in blocks of data that are efficient for analysis. This graphic is an example, where the product code is listed once, then the variants are listed next to it. For analysis, we need to generate a column that has the product code in every row that has other data. The formula in the middle box of the graphic does this. It roughly translates as "if the cell to the right is not blank, use it" (this picks up the product code where it appears) "otherwise, if the corresponding [...]
Usually, when you write a formula, you refer to a range of cells, e.g. =SUM(A1:A5). However, if you are continually adding new rows to the data, you don't want to be constantly updating the formula. Well, why not make the formula refer to the column, rather than a range - then it'll include anything in the column. Example: =SUM(A:A) will add up everything in column A. It works for rows, too, e.g. =SUM(1:1). Note that, when defining a column or row in Excel, you need to define the start and finish column/row, separated by commas - even if they are the [...]
