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 [...]

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 [...]

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 [...]

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 [...]

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 [...]