Custom number formatting: Getting Excel to show date and day in one cell

If there is one skill you have to learn that will really make a different to the quality of your Excel work, it is learning to use custom number formatting. Here is one example.

It often happens that I have a list of dates against which data are going to be entered, but I would like to see the weekday as well (this will help me with the data entry).

An example is shown in Figure 1. My traditional approach, which is evident there, was to add a column and use the formula =TEXT(B3,”DDDD”) or =TEXT(B3,”DDD”) to show the day.

Date and day in two columns

Figure 1    Column to show day of week

About a year ago, I thought to myself that this extra column was really unnecessary, and that I should try to remove it. My immediate thought was to try and combine the date and day in the Date column, and I realised immediately that the only way for me to do that would be with custom number formatting. I knew that dates are stored in Excel as a serial number counting from 1 January 1900 (albeit with one intentional error—see here as well for some more info, and note Microsoft’s “diplomatic” choice of words!). So, for example, the date in cell B3 (2015-01-01) in Figure 1 is stored as the serial 42005. The display of the date is added with cell number formatting. So then the thought came to me that I could try using two different date-style custom number formats in one cell together, and it worked.

So the date format already added by Excel is yyyy/mm/dd, and I decided to try something like ddd yyyy/mm/dd, as seen in Figure 2, which also shows the result in column B (and I have already deleted the “Weekday” column which was in column C). the ddd format tells Excel to show the cell value in the short day format—I could also use dddd for the full day.

Figure 2    Adding a custom number format to show both day and date

That’s a simple change, but it is very effective. Now I only need the date column, and don’t have to copy down additional values in the next column to keep up to date with it, if you will excuse the pun.

Share

Using superscripts in Excel’s custom number formats

One of the most under-appreciated features of Excel, I think, is Excel’s custom number formatting.

I will be alluding custom number formatting in a future post series as well, but here is a quick custom number formatting tip.

Let’s say I want to work with area or volume, and need to indicate that using custom number formatting (e.g., m2, or cm3). How do we add those in Excel?

Note, here, that I am talking specifically about custom number formatting. I can type m2 very (well, relatively!) easily in Excel—all I need to do is edit the cell, type “m2” and then select the 2, and use the Format Cells dialog to make it superscript. But that is not what we are looking at here. Say I have a formula, and whatever value the format provides, must be shown together with the m2 suffix. Or I have a blank cell, and when I enter a value (e.g., 3), then it is displayed with the custom format suffix (i.e., 3m2).

The problem, of course, is that we use the Format Cells dialog to create the custom format, and we also use the Format Cells dialog to add the superscript, and the dialog cannot be invoked on itself (i.e., we cannot format the 2 to be superscripted while entering it into the custom number format box in the Format Cells dialog).

Your first thought might be: Type the m2 and then format it to be superscripted as described above, copy it, and then paste it into the custom format section of the Format Cells dialog. But a long-standing gripe I have with Excel is its inability to really work with rich text, and when you copy text while editing a cell, all formatting (such as bold, italics, or superscript) is lost.

So we need a different approach.

To do this, we start, instead, with the Insert Symbol dialog. Because the Format Cells dialog is modal, we cannot invoke the Insert Symbol dialog while adding the custom number format (so we cannot use Insert Symbol to add the superscripted two to the Format Cells dialog). But what we can do is learn the character code of the 2. So I open the Insert Symbol dialog. Figure 1 shows this dialog, with the Superscript Two symbol selected. Note that, if I wanted something like cm3, the Superscript Three symbol is there as well.

Figure 1    Insert Symbol dialog

The next step is to find out what the ASCII code of this symbol is. Of course, we could look this up in a table, but I am using Excel here to find it. Nonetheless, in the From list box, I choose ASCII (Figure 2). That shows (in the Character code box) the value 178. We could, of course, insert the value into a cell, and then use the =CODE function to get the same information, but this is slightly quicker.

Figure 2    Insert Symbol dialog showing ASCII codes

Once I have this information, I can use standard custom number formatting procedures to create my format. So, for example, showing a number with one decimal, I would use the format #.0. I would then just add “m2” to that (including the quotation marks). I add the 2 by holding down the Alt key while typing 0178 on the number keypad. Figure 3 shows the creation of this custom number format in the Format Cells dialog.

Figure 3    Adding the custom number format

And, to wrap it up, Figure 4 shows the custom number format in use. Note that $B$2:$C$10 use a custom format #.0″m” and $D$2:$D$10, $E$2 uses to format I just demonstrated, #.0″m2“.

Figure 4    Custom number format in use

Share