Chris

Source: http://www.tiglo.co.uk/undocumented-excel-convert-month-name-to-number/

If you have a month name in cell A2, put this in cell B2 to get the month number:

=MONTH(1&A2)

Originally posted on Dreamwidth.

There may be a better way to do this, but whatever.

In Excel, I have column A, which shows all amounts, and column B, which shows the date the transaction was cleared.  (Mmm, banking.)  I wanted to show a total of what had not yet cleared.

So.

=Sum(A:A) - SumIf(B:B, "<>", A:A)

- Vista 64-bit.
- Excel 2007.
- MyODBC 3.51.  (We're using MySQL 3.23.)

Steps:

- Go to Data Sources (ODBC) and add a user data source to connect to the db.
- Test the connection - it works.
- In Excel, go to the Data section, and click From Other Sources.
- Choose From Data Connection Wizard.
- ODBS DSN, next.

Suck:

No matter what I select, clicking Next gives me nothing.  No delay, no display, no closing windows, no hanging, nothing.  This isn't just MySQL (though I listed it to be thorough) - no option does anything.

What &$(# gives?

The formula to return the text day of the week for a given date in Excel:

=CHOOSE(WEEKDAY(A1, 1), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")

(Also totally works in Google Spreadsheets.)

The WEEKDAY(date, type) function takes a date and returns the number of the day of the week.  So WEEKDAY("2009-06-10", 1) would return 4 - Wednesday is the fourth day of the week.

type in that formula:

* 1 or no value == 1 (Sunday) through 7 (Saturday)
* 2 == 1 (Monday) through 7 (Sunday)
* 3 == 0 (Monday) through 6 (Sunday)

(So US-centric.)

CHOOSE(index#, [item1, item2, ...]) takes two things - an index number, and a set of values to choose from.  (No brackets, though - that's just for ease of descriptifying.)

*makes "and so therefore" gesture*

In Excel, in VBA, I have one function and one subroutine.  They should never talk to each other.  The function calculates a date thing in cell A1, and the only time the subroutine touches A1 is to get the current value.

So why is it when I ever modify any contents anywhere, nowhere near A1, the function runs?  The first thing I do is empty some cells out, and the function runs.  Qua?

Hooray for formulas.  My Google spreadsheet now has two pages: one for figuring out repeating designs, and one for resizing Excel grids both by pixels and by Excel's arbitrary row height/column width numbers.

Technically, height and width assume a font of Arial 10 - width is how many numbers could fit in the column, and height is the height of the font in points, where one point is 1/72".

I don't know why I know this.  Anyways, here you go.

What's next?

Random bits of life that are not about me being depressed:

* We watched Chronicles of Narnia last night.  B accurately guessed that I would want to put Mr. Tumnus in my pocket and pat him.  *pat pat*

* I am almost finished with my lovely Excel spreadsheet.  ( Here's what it does. )

That cut, summarised: I'm a big huge honkin' goddamn dork, and there's no denying it.

* I have a cute kitty.