Search other Excel resources (AccountingWeb, Beancounters Guide, IT Counts):

Excel Christmas special: countdown to Christmas

Introduction

A couple of people have come up to me recently during my Excel lectures and reported that they had recently attended a lecture where the lecturer put a 'clock' on the screen during breaks to count down to the resumption time.

Given my lectures are about Excel, it struck me that I should be able to come up with an Excel based countdown procedure and that a pie chart was the obvious place to start.

Automatic pie chart clock

First of all we create the data for the pie chart based on the start time, the end time and the current time. The current time is entered using the NOW() function which includes date and time and is recalculated whenever the workbook as a whole is recalculated.

We have calculated the elapsed time and the remaining time by subtracting the start time from the current time and the current time from the end time respectively. We've then formatted the results to show the number of days and hours, minutes and seconds.

Next, we create a pie chart based on the two calculations. We've formatted this to have no border and to use a picture for the 'fill' of the elapsed time data point. The remaining time data point has a simple white background. We've included data labels for both data points and set the format to be based on the source cells. We've then just positioned the labels so they are visible:

Now all we need to do is to automate the countdown. We could do this using VB code but instead we'll use a method that doesn't use any code. We'll create an external data link to a named cell in the same workbook, and then set the refresh properties to 1 minute:

Whenever the link is automatically refreshed the workbook is recalculated, causing the NOW() function to return the current time and update the pie chart.

You could go a bit further and divide the total duration into 4 quarters or even 12 sections and put each into a single cell. You could then position text boxes around your pie chart 'clock' and select each in turn and then type equal and the reference to the appropriate quarter or section in the formula bar. E.g.: =F1:

With very best wishes for Christmas and the New Year