Auto-setting the Date for a Dashboard

If you are like me you frequently have to update various excel based dashboards. In the pursuit of even more automation, I thought it would be fun to make Excel figure out the current date and automatically set the date of my dashboard to the end of the previous week.

All you need is three Excel formulas:
WEEKDAY()
TODAY()
IF()

The first step is to have excel figure out the current date:
=WEEKDAY(TODAY())

Then we calculate how far away we are from the last day of the week. The WEEKDAY() formula calculates a value from 1 to 7. If 7 is the last day of the week, by simply building out a nested IF() formula we get the magical result.

=IF(B4+1=7,TODAY()-6,IF(B4+2=7,TODAY()-5,IF(B4+3=7,TODAY()-4
,IF(B4+4=7,TODAY()-3,IF(B4+5=7,TODAY()-2,IF(B4+6=7,TODAY()-1,Ooops))))))

Fun eh?

Here is a link to a Google Document that lays out the steps.

Be Sociable, Share!

Leave a Reply

Your email address will not be published. Required fields are marked *