In order to make effective use of calendar arithmetic, you need to think about it at two levels: managing formulas on the spreadsheet and integrating those formulas with the visual components on the dashboard.
Setting Up Calendar Arithmetic on a Spreadsheet
This section poses an interesting problem and its solution. Instead of focusing on the prob- lem itself, we focus on how the problem is structured in a spreadsheet setting.
Think about how many days there are between today and New Year’s Day. The problem is not difficult to set up on a spreadsheet when you know you can specify dates (and their serial numbers) for both the present day and the upcoming New Year’s Day. If you specify the problem correctly, you can write a formula that doesn’t hardwire the year the question is being asked.
If the current day is 2/21/2008, the subsequent New Year’s Day is 1/1/2009. If the current day is 2/21/2009, the subsequent New Year’s Day is 1/1/2010. At first glance, it would appear that the number of days for both these scenarios would be identical, but they’re not because 2008 is a leap year. You could take into account calculations with leap years, but that’s not really putting calendar arithmetic to use.
To begin looking at the problem, determine the date of today, which you can get with this function:
=TODAY()
Determine the date of the upcoming New Year’s Day, which is on the first day of January of next year. Next year can be calculated using the following:
=YEAR(TODAY())+1
So the date for New Year’s Day for next year is found as follows:
114 Chapter 4 Embedded Spreadsheets: The Secret Sauce of Xcelsius 2008
4
All you need to do is subtract the day number of the “today” date from the upcoming New Year’s Day date, and you’re done! Here is a formula that gives it to you:
=N(DATE(YEAR(TODAY())+1,1,1)-TODAY())
Alternatively, you could split this out into two formulas:
=N(DATE(YEAR(A1)+1,1,1)-A1)
where A1 has this formula:
=TODAY()
Splitting up complicated formulas is a good practice for the following reasons:
■ The shorter formulas are easier to follow and less prone to accidental errors when you’re typing in the formulas.
■ You can potentially eliminate otherwise repeated computations. Notice in the original formula that TODAYhas to be computed twice. When it is split from the original for-
mula, the computation is done only once.
■ The split-out portions are accessible to formulas in other spreadsheet cells.
Calendar Arithmetic at the Dashboard Level
To get a better handle on calendar arithmetic at the dashboard level, think about how you get a dashboard and a spreadsheet to mesh with one another.
Here’s an interesting problem: Say that you’re running a call center or help desk operation, and you’re creating a dashboard with a Calendar component that provides a specific phone number, based on whether the day is a weekday, weekend, or holiday. If it is a holiday, you could assign a specific phone number to each of the holidays (see Figure 4.16).
Figure 4.16 Conditional matching of dates with the Calendar component.
115 Making Dashboards Date and Time Aware
4 In the file ch04_ConditionalMatching.xlf, the Spreadsheet Table component shows the
underlying computations while the dashboard is running live. When you select a date in the Calendar component with your mouse or arrow keys, the corresponding message is dis- played to the right of the calendar. As you select a date from the Calendar component, the date is placed onto the input cell C1.
The date in cell C1 can match with one or more of seven possible scenarios (five are holi- days, and the remaining two are weekdays or weekends). If there is a match, the correspon- ding scenario number appears in column G. The formula (starting with cell G4 through G8) used for this matching is as follows:
=IF(E4=$C$1,A4,””)
Effectively, the formula says the following:
=IF(HolidayDateInColE=SelectedCalendarDate,ScenarioNumber,””)
The formulas for scenarios six and seven are slightly different. They determine whether the date in the Calendar components is a weekday or weekend. Notice that 1/1/2009 is both a holiday (New Year’s Day) and a weekday, so two conditions simultaneously match. Assuming that holidays are given greater priority, you can take advantage of the scenario numbers for holidays being smaller than the numbers for a weekday or weekend. Cell G1 identifies the appropriate scenario number, using the following formula:
=MIN(G4:G10)
When you have identified the scenario number, you can retrieve the appropriate text message for display. This is done in cell H1, using the following formula:
=OFFSET(H3,G1,0)
The OFFSETfunction is discussed later in this chapter. Other functions, such as INDEX, could
also perform this lookup.
The contents of cell H1 are displayed in the Label component on the dashboard.
N O T E
So far, I have not concentrated on the aesthetics of the dashboard design; that is left for later chapters. I haven’t placed an emphasis on the details of the spreadsheet formulas. Instead, I’ve focused on the overall setup.
In columns B, C, and D, you place numeric values for the year, month, and date, and you construct a date in column E from these values. You could have hardwired the dates in col- umn E and omitted the information in B4:D8. By hardwiring the dates, though, you would lose the advantage of being able to dynamically set the date at runtime. This could be set from either a formula or by a user interaction with a visual component on the canvas. You are also taking advantage of positional arrangements so that if more then one match is detected, the one with the highest priority prevails.
116 Chapter 4 Embedded Spreadsheets: The Secret Sauce of Xcelsius 2008
4