Nifty way to prepare dates for MS Excel PivotTables

I often create PivotTables in MS Excel. I have had a recurring hassle when I use dates and want to analyse data by month. If I simply format my column to show only month and year, the pivot table will not group my information together.

Formatted date in MS Excel

The custom formatting displays month and year, but the Format bar shows a full date and time.

Pivot Table 1

I end up with three instances of July 2013 – one for each different date and time

Historically I would create a new column, type in the month, and then copy and paste for each row in that month. But I have now found a much better way to work around this problem:

  1. Select the column heading for the date column (e.g. column C).
  2. In the menu bar select Format > Cells. Select the Custom category. Enter your preferred format in the Type field (e.g. mmm yy). Click OK.
  3. Press the Ctrl + A keys on the keyboard to select all text. Press Ctrl + C to copy.
  4. Open a blank MS Word document. Press the Ctrl + V keys to paste the text. MS Word will represent the dates with your preferred formatting.
  5. Press the Ctrl + A keys again, then Ctrl + C keys again to select and copy the text.
  6. Return to MS Excel. Press Ctrl + V to paste.
  7. Create your PivotTable. The dates will now display in the correct format.


MS Excel dates in correct format

After copying through MS Word, the Formula bar now shows 01/07/2013 for all July cells.

Pivot Table 2

The PivotTable now shows one row for each month.

Mystery solved: Runtime borders on Adobe Captivate rollover slidelets

For years now I have been aggravated when using rollover slidelets in Adobe Captivate because they kept adding these ugly blue borders, even if I turned off the runtime borders.

An example of the dreaded runtime border

I have finally worked out what I was doing wrong.  There are TWO Show Runtime Border checkboxes.

  • One for the rollover area, under the Transition heading in the Properties panel.
  • One for the slidelet, under the Fill & Stroke heading in the Properties panel.

 

If you deselect both checkboxes, the problem goes away.

The checkbox in the Transition panel  The checkbox in the Fill & Stroke panel