Dates in Report Headers

When you format the widths of columns in a report, such as a board report, you have to be careful with dates to make sure they are correctly displayed.

The problem arises when a date (or a number) is too large to be displayed in a particular cell and all that is returned are hashes.

Taking a page from the iFRC board pack as an example and looking at the header 'iFRC Ltd - Executive Summary : for period ending 31 January 2017', you can see in the picture below that the column widths have been sized to meet our reporting requirements. For presentational purposes it allows us to keep certain column widths the same, e.g. financial numbers, spacing columns, etc.. It also ensures that the size of each page is consistent.

We have converted the header to text so that we don't have to resize columns for date issues. We have converted the date in the header in to text by using the following formula:

=Co1_Full&" - Executive Summary : for period ending "&TEXT(MonthEndDate,"dd mmmm yyy")

The crucial part of the formula relating to the date is:

&TEXT(MonthEndDate,"dd mmmm yyy")

This converts the named cell 'MonthEndDate', which is our month end date, in to text and formats the date as a long date which is determined by the "dd mmmm yyy" part of the formula.

The full formula can be seen in the formula bar below:

We recommend converting dates to text in formal reports to ensure that the date is presented correctly and that #Errors are not reported.

Usually the quick way 'to fix' the #Error problem is to adjust the size of widths of the columns by adding pixels to one column and taking them away from another to keep the overall size the same or worse still by merging cells!

To illustrate this if we remove the part of the formula relating to the date and reference it directly in cell L3 it causes us an issue because the date has partly overwritten the text to the left and the 'g' is missing from 'ending' as well as the date being too large for the cell itself.