Using Dates in Dynamic Headings

& symbol and the TEXT function

By Neale Blackwood

Excel stores dates as numbers and when you try to use a date in a heading with the & symbol Excel will display the number, not the date. Hence, if cell A1 contained Sales For and A2 contained 1/1/08 then the formula =A1&” “&A2 would display Sales For 39448. To have Excel display a date you need to use TEXT function.

In the above example = A1&” “&TEXT(A2,”mmm yy”) would display Sales For Jan 08. The formats used in the TEXT functions are the same as those in the Format menu > Cells > Number tab > Custom category.

Using this method you can automate your monthly headings by linking them to a single cell that has the current month.