Category: Office

It doesn’t come up very often as I do a lot of work using SQL Server rather than Microsoft Excel, but every so often I do need to do some date manipulation in Excel.

In the previous article of the Excel Snippets series, I showed how to get the first and last dates of the calendar year. One of the peculiarities of the UK, is that our tax year always starts on the 6^th of April.

The formula to accurately get the start of the tax year is more involved than getting the sart of a calendar year, but we can break it down to make it easier to understand.

To start with we get the year of the supplied date using the YEAR function:

=YEAR( TODAY() )

Then we check if the supplied date is before the 5^th April of that year:

=TODAY() <= DATE( YEAR( TODAY() ), 4, 5 )

The date function is supplied three parameters: year, month and day, which in the above example is the output of the YEAR function, 4 and 5 for the 5^th April.

This will return TRUE if it is and FALSE if it isn’t, which will effectively return 1 or 0; in the formula we subtract this from the result of the YEAR function which will give us the correct year:

=YEAR( TODAY() ) - ( TODAY() <= DATE( YEAR( TODAY() ), 4, 5 ) )

We can then wrap this in another DATE function which supplies 4 and 6 for the month and day to output the first day of the tax year:

=DATE( YEAR( TODAY() ) - ( TODAY() <= DATE( YEAR( TODAY() ), 4, 5 ) ), 4, 6 )

To get the last day of the UK tax year, we just need to copy the above formula, add a +1 to the output of the first YEAR function and change the day parameter in the outer most DATE function to 5[/:

=DATE( YEAR( TODAY() ) + 1 - ( TODAY() <= DATE( YEAR( TODAY() ), 4, 5 ) ), 4, 5 )

Updated 13/06/2023: After my error was pointed out, I’ve done some investigating and updated the post above; the formula here now is one posted by Brad Scott in response to a question on a Microsoft Community question.

It doesn’t come up very often as I do a lot of work using SQL Server rather than Microsoft Excel, but every so often I do need to do some date manipulation in Excel.

I recently needed to calculate the first and last dates of a year from a user supplied date for a Jet Report. This can easily be done using a combination of the YEAR and DATE functions.

The YEAR function can be used to extract the year from a supplied date:

=YEAR(TODAY())

The date function is supplied three parameters: year, month and day.

The output of the YEAR function gives us the first parameter and for the month and day of the first day of the year we can simply supply the number 1:

=DATE(YEAR(TODAY()),1,1)

To get the last day of the year, the month parameter is set to 12 and the day to 31:

=DATE(YEAR(TODAY()),12,31)

I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.

This is based on an example a colleague discussed with me for dynamically calculating the sum of a range of columns based on the user specificed number of months.

The data for the months of the year was going across the worksheet with a total at the end, but he only wanted to total up the rows up to the required month. The example below shows the required output, with the user specifying month 7 (July) in cell B1 and the required total value in cell O3 which is the sum of C3:N3:

If we’d wanted the whole row, this would have been quite easy to do using the SUM formula:

=SUM(C3:N3)

The problem is though, that the second cell in the range needed to be variable. Fortunately, Excel has another function which we can use; that function is OFFSET:

=OFFSET(cell reference, rows, columns))

In our example, we needed to set the cell reference parameter to B3, the rows to 0 and the columns to B1:

=OFFSET(B3,0,B1))

This OFFSET will, when combined with the SUM formula, give us the result we need of summing from B3 through the columns to I3 when month 7 (July) is speficied by the user in cell B1:

=SUM(C3:OFFSET(B3,0,B1))

I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.

A colleague was recently working with a client who was preparing data for import. One of the issues they encountered when cleansing the data was that Excel stripped the leading zeros from the ID fields and were looking for a way to add them back in order to import the file with the required ID format.

They were looking for a formula to do this and came up with an approach of prepending 0s and using the RIGHT function, but there is a claaner approach using the TEXT function.

The TEXT function is very flexible and one I’ve used before when formatting numbers to two decimal places.

The ID numbers were a variety of lengths, but needed to be formatted to six characters long with zero prefixes.

This can be simply done using the TEXT function as shown here:

=TEXT($A52,"000000")

I had a strange occurrence recently with Microsoft Excel where the column headers changed from letters to numbers. I’d didn’t notice initially, but it was when I was working with a formula that I realised it wasn’t putting letters in the cells but an odd numbering style.

When I looked into why I discovered the column headers were numbers:

Continue reading “Microsoft Excel Column Headers Showing as Numbers Instead of Letters”

When working with a large document such as a book, a common requirement is to add an index to the end so that important words or phrases can be indexed for easy lookup. While you can tag words for the index within Microsoft Word a better way to do in bulk is to use a concordance file.

This is basically a separate two column file containing a list of words to index in the first column and how they should be shown in the second:

Continue reading “Applying a Word Concordance File to Create an Index”

I hated threaded email when it first came out, but I’m now used to it and as it turns out prefer it to non-threaded. I discovered this after changing jobs; my old work laptop had threaded emails enabled and the one at the new job didn’t.

I worked that way for a few weeks and it was fine as I was doing a lot of learning and not involved in many projects. As soon as that changed and I started getting lots of emails I wanted to enable threaded emails, but had some trouble finding the option.

I eventually found it and there were two reasons why I was having problems finding the setting; the first was that I was looking in the Options and the second was that I was looking for something which included the word “thread”.

It turns out the setting in the Outlook desktop client is on the View tab and is called Show as Conversations:

Continue reading “Enable Email Conversations in the Outlook Desktop Client”

I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.

I’ve been using this formula for quite a while now, but have to admit that I did not create it and no longer remember from where I got it.

The basic use is that you would have the starting amount in cell C2 and then use this formula in the next cell down and replicate down the page and it will calculate interest on a monthly basis.

The first highlighted section is the % rate being charged on the mortgage and the second is the amount you’re paying each month.

=IF(C3<=0,0,ROUND((((1+({% rate}/100)/12)^(12/12))-1)*C3,2)+C3-({mortgage payment amount}))

I’ve used this formula for a while to calculate an estimate of my mortgage it calculates to within a reasonably close amount (usually within a handful of pounds) as my mortgage interest is calculated daily, not monthly, but I’ve not been able to work out/find a formula for daily interest.

I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.

I was have a tendency to track personal financial data in Excel and was recently updating my electricity usage sheet and wanted to set a date column to auto incremement by one month for each row. I did some checking and found that the EDATE function can be used to get the same date of the previous or future months based on the second parameter.

The structure opf the function calls is shown below:

=EDATE(start date, number of months)

For example, if I wanted to add one month to todays date, I would use the following:

=EDATE(Now(), 1)

I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.

Most, if not all, of the programming/scripting languages I work with will have a function allowing you to calculate a difference between two dates, but I always have to look them up.

It’s not too difficult in Excel; this formula can be used to work out the number of days difference between the values in cells A2 (start date) and A3 (end date):

=DATEDIF(A2,A3,"D")

The highlighted section can be several values depending on how you want the date difference calculated:

Unit	Returns
D	The number of days between the supplied dates.
M	The number of months between the supplied dates.
Y	The number of years between the supplied dates.
MD	The number of days between the supplied dates ignoring the months and years.
YM	The number of months between the supplied dates with the days and years ignored.
YD	The number of days between the supplied dates with the years ignored.

More details and examples on these units are available from the Microsoft Support article which notes they don’t recommend MD, but, to be honest, I don’t see much use for any of the last three listed above.