Show Day Names With Your Dates (XL2000-XL2007)

If you ever need to show the name of the day in addition to the dates (e.g. Saturday, July 10) here are a couple of options.

Option 1: You can create a custom number format for the cells containing the dates

1) Press CTRL+1;

2) Select the Custom category;

3) In the Type field enter dddd, mmm y

Option 2: You can use the TEXT function to return a custom date based on a date in another cell.

Type =TEXT(A1,”dddd, mmm y”) where A1 is a cell containing a date.

With both of these options, you have lots of flexibility for creating date formats by using any combination of the following codes.

d – will return the day as a number without a leading zero (e.g. 1)

dd – will return the day as a number with a leading zero (e.g. 01)

ddd – will return the abbreviated weekday (e.g. Sun)

dddd – will return the full weekday (e.g. Sunday)

m – will return the month as a number without a leading zero (e.g. 1)

mm – will return the month as a number with a leading zero (e.g. 01)

mmm – will return the abbreviated month (e.g. Jan)

mmmm – will return the full month (e.g. January)

yy – will return the year as a 2-digit number (e.g. 10)

yyyy – will return the year as a 4-digit number (e.g. 2010)

You can also include commas, dashes, slashes, parentheses, within the format code. For example, the code (dddd) mmmm d, yyyy will return (Saturday) July 10, 2010.

via Microsoft Excel Tips.

The Excel Camera tool enables you to take a live picture of a range of cells that updates dynamically while the data in that range updates. Although Microsoft doesnt include the Excel Camera tool in the mainstream Ribbon, its quite useful when you want to build Excel dashboards and reports. Before you can use the Camera tool, you need to add it to your Quick Access Toolbar QAT.The Quick Access Toolbar is a customizable toolbar in which you can store frequently used commands so that theyre always accessible with just one click. You can add commands to the QAT by dragging them directly from the Ribbon or by going through the Customize menu.Follow these steps to add the Camera tool to the QAT:1. Click the Office icon in the upper-left corner of Excel.2. Select the Excel Options button to activate the Excel Options dialog box.3. Click the Customize button.4. In the Choose Commands From drop-down list, select Commands Not in the Ribbon.5. Scroll down the alphabetical list of commands and find Camera; double-click to add it to QAT.6. Click OK to close the Excel Options dialog box.When youve taken these steps, youll see the Camera tool icon in your Quick Access Toolbar. Not surprisingly, the icon for the Camera tool looks like a camera.

via Adding the Excel Camera Tool to the Quick Access Toolbar – For Dummies .

I decided I needed a button in my menu bar to turn calculations to manual or auto. As I am now using Excel 2007 the process to get to the Option dialogue is a little long winded.

So my code looks like this;

Sub CalcChangeStatus()
Cells(1, 1).Select
If Application.Calculation = xlManual Then
Application.Calculation = xlCalculationAutomatic
Else
Application.Calculation = xlManual
End If
End Sub


Add a button to a menu bar and link to this macro. The button will then toggle the calculation status from Auto to manual.