Daily Dose of Excel

I’m graphing some temperatures and need to display them with the degree symbol, but they need to remain numbers (not text) so they can be graphed. Here’s the number format:

+###°C;-###°C;_+0°C

The output looks like this:

60    +60°C
0    0°C
-20    -20°C

The three parts of the number format are separated by semicolons. The first part defines the format for positive numbers, the second part for negative numbers, and the third part for zero. The second and third parts are optional, as is a fourth part that defines the format for text.

To make the degree symbol, hold down the Alt key and type 0176 on the numeric keypad.

In the third part, I left enough space for a plus sign. To leave space, I include an underscore followed by the character whose width is the amount of space I want. I did this so that if the numbers are left aligned the C’s would line up. They don’t line up because for a couple of reasons. First, there can be multiple digits in the positive and negative numbers. Second, the plus sign and minus sign take up take up a different amount of space in Tahoma.

I’d like to come up with a format that lines up the C’s regardless of the cell alignment. I’d even be willing to limit the temperatures to -99 to +99. I assume this has will use conditions, but I couldn’t figure it out. Any ideas?

Over on Swimming Splits, Olly asks:

I am a rowing coach and am putting the data that my crew gives me from their training sessions into a spreadsheet in the mm:ss.00 format. However, if I make a mistake inputing the data e.g type 1:23.03 when it should be 1:23.30 something weird happens.

When I click in the cell, the value rounds up or down depending so 1:23.30 will round to 1:23.00. This will have a massive impact on the results that I am looking at. Is this a common occurrence in excel or is there something that I am doing wrong or should be pressing?

Good question. I’m not sure that I know this answer definitively, but I’ll tell you what I think based on what I see.

Dates and times are strange beasts in Excel. I should say that they are a strange beast because they are the same thing. As you probably already know, dates are stored as the number of days from a particular date, usually 31-Dec-1899. That means that 01-Jan-1900 is stored as 1 and 04-March-2007 is stored as 39,145. Similarly, times are stored as fractions of a day. While dates are integers, times of day are the fractions between the integers. Today may be 39,145, but today at 8:00AM is 39145.3333333. It’s only 8:00AM and already a third of the day is gone.

This method is great for adding, subtracting, and generally doing other math operations on dates. What it’s not good for is understandability by most humans. Excel attempts to bridge that gap by displaying dates and times as dates and times rather than these cryptic numbers. Note that when you enter 0:01.03 in a cell, the formula bar displays 12:00:01 AM. Usually the formula bar will tell you the truth regardless of how you’ve formatted the cells, but not in the case of dates and times. Like an over-protective mother, it shields you from the harsh realities. The noteworthy aspect of this example is that there are no hundredths in the formula bar. The hundredths are still stored, but they aren’t displayed in the formula bar.

If you were to edit the cell, say, by pressing F2 and Enter you lose the hundredths. Pressing Enter is the same as entering 12:00:01 AM into the cell, which contains no hundredths. To change the cell from 3 hundredths to 3 tenths, follow these steps: F2 to edit the cell, backspace three times to remove the AM and the preceding space, type .3, press Enter. I don’t know of any way to get the hundredths to display in the formula bar.

It seems rather easy for Microsoft to have done this differently. If a user enters fractions of seconds, display fractions of seconds. Otherwise display it as it is now. There doesn’t seem to be a trade-off here that I can see (other than coding time by developers).

I had a worksheet table with blank rows separating the groups.
I needed to add another column - a formula - but wanted to retain the blank rows for formatting tidiness.

The table after adding the formula column:

Notice the formula produces zeros for the blank rows. I could just select each cell (D3, D5, D8, D10) and hit delete, but for thousands of rows that would be time consuming.

Here's how I did it:
- Select column C.
- From the Edit menu, select Go To..., then click Special...
- Select Blanks, then click OK

Then I ran a macro which allows me to move the selection over one column.
In this example, I typed 0, 1 for the Input to SelectionOffset.
After the Selection was moved, I hit the delete key.

El Says:
"I have a spreadsheet with dates and on each day, there are several activities with start time and end time. I want to add a row between each day, and also to add up the total time for all the activities in each day."

This is where the Subtotals feature is quite useful.
It can add a variety of footers to appear at breaks in the group. You get outlining too.
Select your range, then from the Data menu, choose Subtotals...
At each change in StartDate, Use function Sum, Add subtotal to Duration.

From time to time, I am asked to audit a spreadsheet.

One of the common mistakes I see is an inconsistent formula inside a group of cells.
For example:
- A1 has the formula =G1 * 0.105
- A1 has been formula auto-filled to A1:E5
- Then some time later, cell A3 has been changed to =G3 * 0.107

So the assumption is that A1 can be safely auto-filled to the region of cells. However, we have an exception which should be noted.

It's nice to be able to highlight these exceptions. Here are 2 ways.

1. If you have Excel XP and above, you can use the Error Checking options. A little green triangle appears telling you that "something strange" is going on.
One downfall of the Error Checker is that it wont trigger if the inconsistent formula is on the corner of the checked region. In our example above, if the changed formulas was A5 instead of A3, it would have ignored that inconsistency.
I assume that this is to accommodate subtotals and grand totals?

2. Write a bit of VB code using the idea that the R1C1 version of the formula should be identical for all cells in the selection, so it should be a simple loop to check all of the formulas.
Here is an example:

To use it, select the range A1:E5 then run the macro. It would colour the inconsistent formulas yellow (in the first example, cell A3).

Here's what my (partially obfuscated) desktop looks like:

I put information I need on my desktop so I'm a Windows+M key combination away from what I need. The Excel file sits in my XLStart directory so the calendar gets updated every day.

VBA from Excel to Desktop

In the Workbook_Open event, I have

It only updates it once per day, although it doesn't really take that long to execute. In a standard module, I have:

The PastePicture call uses code from Stephen Bullen's PastePicture.zip file.

Almost nothing in the file is my own - all stolen. In addition to Stephen's PastePicture, the following code is borrowed:

The Calendar

The calendar for this month and next month is John Walkenbach's Array Calendar (see also). For 'next month' I merely added 1 to the month in the DATE function throughout that formula.

Week Numbers

The week numbers along the right come from Ron de Bruin's site. The formula in H27 is

=IF(LEN(B27)=0,"",INT((B27-DATE(YEAR(B27-WEEKDAY(B27-1)+4),1,3)+WEEKDAY(DATE(YEAR(B27-WEEKDAY(B27-1)+4),1,3))+5)/7))

It only shows the week number for weeks with a Monday (B27 in this case).

Highlighting Today and Holidays

The conditional formatting for the current month looks like this:

The first format inverts the colors for the current day. The second format checks the range HolVac to see if the date is a holiday - meaning a day I don't work. In that range, I have the holidays my company offers plus I enter any scheduled vacation days. I show the holiday formulas below. The second format is repeated for 'next month'.

Special Characters

Because I deal with German companies on a somewhat regular basis, I like to keep the ASCII codes for some German characters handy, and I do that across the bottom. To enter the special characters, hold down the Alt key and type the four digit number on the numeric keypad.

Holiday Formulas

The formulas for the holidays come from Chip Pearson's Holiday Page and Chip's Date and Time Page. In the following formulas, cell S1 contains the current year and S2 contains the next year.

New Year's Day: =IF(WEEKDAY(DATE(S1,1,1))=7,DATE(S1,1,1)-1,IF(WEEKDAY(DATE(S1,1,1))=1,DATE(S1,1,1)+1,DATE(S1,1,1)))

Next New Year's Day: =IF(WEEKDAY(DATE(S2,1,1))=7,DATE(S2,1,1)-1,IF(WEEKDAY(DATE(S2,1,1))=1,DATE(S2,1,1)+1,DATE(S2,1,1)))

Memorial Day: (array) =IF(SUM(IF(WEEKDAY(DATE($S$1,5,1)-1+ROW(INDIRECT("1:"&TRUNC(DATE($S$1,5,31)-DATE($S$1,5,1))+1)))=2,1,0))=5,
    DATE(YEAR(NOW()),5,1+((5-(2>=WEEKDAY(DATE(YEAR(NOW()),5,1))))*7)+(2-WEEKDAY(DATE(YEAR(NOW()),5,1)))),
    DATE(YEAR(NOW()),5,1+((4-(2>=WEEKDAY(DATE(YEAR(NOW()),5,1))))*7)+(2-WEEKDAY(DATE(YEAR(NOW()),5,1)))))

US Indpendence Day: =IF(WEEKDAY(DATE(S1,7,4))=7,DATE(S1,7,4)-1,IF(WEEKDAY(DATE(S1,7,4))=1,DATE(S1,7,4)+1,DATE(S1,7,4)))

Labor Day: =DATE(S1,9,1+((1-(2>=WEEKDAY(DATE(S1,9,1))))*7)+(2-WEEKDAY(DATE(S1,9,1))))

Thanksgiving: =DATE(S1,11,1+((4-(5>=WEEKDAY(DATE(S1,11,1))))*7)+(5-WEEKDAY(DATE(S1,11,1))))

Xmas Eve: =IF(WEEKDAY(DATE(S1,12,24))=7,DATE(S1,12,24)-1,IF(WEEKDAY(DATE(S1,12,24))=1,DATE(S1,12,24)-2,DATE(S1,12,24)))

Xmas: =IF(WEEKDAY(DATE(S1,12,25))=7,DATE(S1,12,25)-1,IF(WEEKDAY(DATE(S1,12,25))=1,DATE(S1,12,25)+1,DATE(S1,12,25)))

Update: Download DesktopPic.zip