Setup Live Table Headers With Date Functions
Are you tired of updating your table headers everytime you update your reports? You can setup live table headers so that they can update themselves. Here is how to do it:
You need to be familiar with Excel Date Functions in order to benefit from this post. If you are not, I advice you to check Excel Date Functions in Use before you proceed for better understanding the concept.
It is always a good practice to take current day as starting point. You can use TODAY() function to get current day.
From here, you can go to anywhere:
Current year: =YEAR(TODAY()) ,last year: =YEAR(TODAY())-1 , next year: =YEAR(TODAY())+1
Current month: =MONTH(TODAY()) ,last month: = MONTH(TODAY())-1 , next month: = MONTH(TODAY())+1
Today: =TODAY() , yesteday: =TODAY()-1 , tomorrow: =TODAY()+1
Here is an example about how to use them in headers:
We have a rolling 6 weeks sales table for our ice-cream stalls. We want headers to adjust themselves every week automatically. So we setup our table headers like show below:
(We use WEEKNUM() function here to fetch week numbers.)
This way our table headers will automatically adjust with each passing week. All we need to worry about is to update sales numbers.
Important: Do not forget that there are certain amount of days, weeks, months in a year. So in the beginning and end of each year, above formula will give you results like Week-2 during first weeks of new year. Similarly, you will get results like Week56 during the end of any year if you setup future dates like =WEEKNUM(TODAY())+4.
To overcome this problem, you should add some controls into your formulas. By using IF() function, we’ll set sum conditional controls. Here is formulas final form:
Above formula checks the week number value. If value is over 52 it subtracts 52 from it, if value is below zero it adds 52 to it, if the value is betweem 0 and 53 then it doesn’t do any correction.
Important: Expect your workbooks to show save prompt whenever you close them after using such formulas. It is beacuse formulas like TODAY() are self calculating formulas and they are calculated automaticallty everytime you open your workbook. So even you didn’t do any changes on that workbook, excel will detect that formula and make calculations, so it will ask you whether you’d like to save.