Brute Force Dates

In many cases, Excel displays dates different than other systems want it.

Usually, your date will look like this: 10/12/2012.

For my international friends, of course, that date would be 12-10-2012, I know. Didn't forget you. :)

But sometimes, you need the date to be 10122012. Date formatting won't always save the day.

The TEXT function, however, can "force" a cell to look any way you darn well feel like it:

=TEXT(A2,"mmddyyyy") would get you 10122012, assuming the date was in A2.

If I want it to be European style:

Play around with the TEXT function, and you'll see all kinds of good things happen.

Creating a Schedule Using Excel

Why not a PivotTable?

Try it. You'll get as far as the Student Name in the row, and the Expression in the Column. Now try getting the Course Name to do anything useful.


I tried a few things:

  • I put the Course name as a secondary Row field. No dice.
  • I tried all forms of summarizing on the Course Name. All it does is show a 1 or 2.

I ended up reading the blog of PivotTable guru Debra Dalgleish, who confirmed what I feared - text field in a PivotTable Value Field? No can do.

Use the INDEX and MATCH Functions

This way works. It's a little hinky, but workable.

First things first - get a unique list of all of your students in column A. Give said column a label, like, oh, I don't know, Student. (Crazy, I know.) I would recommend having that list be in a separate sheet from the Schedule data.

Write the EXP numbers across columns B through the end.

In B2, your formula will involve INDEX, MATCH, and a concept called an array formula, which we've never tackled before. But for those of you who survived the Advanced Functions class, this won't be much of a great leap, honestly.

{=INDEX('Schedule Data'!$B$1:$B$7,MATCH(1,('Schedule Data'!$A$1:$A$7=$A2)*('Schedule Data'!$C$1:$C$7=B$1),0))}


A few things to be aware of:
  1. The curly braces { } are not characters that you enter in. Those are characters that Excel enters, when you press CTRL+SHIFT+ENTER (Mac users, remember, it's Command for you)
  2. Schedule Data is just the name of the sheet where my exported schedule data "lives"
  3. The INDEX function only indexes one column - the column with the course names
  4. The first MATCH function is trying to match the student name in A2 against the list of students in the Schedule Data sheet, where they're in cells A1:A7.
  5. The second MATCH function is trying to match the Exp number from B1 against the list of Exp numbers in the Schedule Data sheet, where they're in cells C1:C7
  6. Why multiply one set of matched values against the other? You're looking for the case where both of those conditions are true
  7. What's up with the 1 and the 0? Same idea. 1 for true, 0 for false. So that assigns numeric values - when the cell is found, it's 1 for true; when it isn't, it's 0 for false.
  8. Why the curly braces? Excel has to evaluate each cell in the range specified, and array formulas let it do that.

I told you it wasn't for the faint of heart. But it works.

Here's the Schedule data as we get it from PowerSchool:

And here's our results: