In the Clean up and Analyze class, we talk a little about the difference between two dates - if I know the student's DOB, I can figure out how old they are; I can figure out whether or not they're eligible for Driver's Education, stuff like that.

Question came up - what about how many days we've been in school? DATEDIF is not sufficient to handle that problem, because, of course, we're not in school on weekends, and DATEDIF counts weekdays and weekends equally.

So what can we do? Use NETWORKDAYS. When you read that function name, it's not "network days," it's "net work days." As in, what are the net number of days between start and end? The implication is, weekends are explicityl excluded, and it's possible to exclude holidays, as well.

How? =NETWORKDAYS(A1,B1,C1:C7) would assume the following:
The start day of school is in A1. The last day of school is in B1. C1:C7 stands for your holidays. Each one would have to be listed out individually, so if your spring break was say, March 15th through March 22nd, each would have to be in its own cell. But you can put in your snow days, there, as well.

Age as a Decimal

In the Cleanup and Analyze class from Day 1, we talked about the DATEDIF function, but a question came up about how to do it as a decimal, that I never really got to answer. I want to take some time and answer it now.

If we assume that .5 is one half of a year, and .75 is three-quarters of a year, then the DATEDIF function can do the job, with a little twist.

The first part is pretty straightforward: =DATEDIF(A1,NOW(),"y"), where A1 is the DOB, NOW() is today's date, and "y" is the increment.

Now, to add the month fraction: =DATEDIF(A1,NOW(),"y")+(DATEDIF(A1,NOW(),"ym")/12)

where the "ym" is the number of months left over after the years are calculated. You divide the remaining months by 12, to get the decimal portion. So if someone's birthday was August 28, 1970, and today's date was 2/28/2011, they would be 40.5 years old; 6 months until their birthday. It's a crude approximation, but it's effective.

Text Functions and Wonders

Locker Combinations, Part 2

What if you have already imported the locker combinations already, and you have to fix them? As you have probably found out by now, when you import locker combinations into Excel, they might get converted into dates; 01-15-24 might become January 15, 2024. Ugh.

Unique Usernames, Enforce The Six Character Limit

Of course, we all know about the 4-1-1 trick - if someone has a name like "Geoffrey G Lilley," then the matter of making a username is pretty easy; lillgg.

What about Jeffrey Wu? That would be just wuj, if we accept the default.

Couple of challenges there - check the length of the last name, check the length of the first name, figure it out.

Here's one quick-and-dirty approach:
=LOWER(IF(LEN(A4)<4,A4&LEFT(B4,4-LEN(A4)))&C4)

In other words, if the length of A4 (the first name) is less than 4, then grab all of A4. Then, take however many characters you need from the first name to make it right, then add the middle initial.

In other words, check A5 (the first name); if that's less then 4, check to see if C5 is blank. If C5 is NOT blank, then use B5, the number of characters needed from B5, then C5. If C5 is blank, use A5, then as many letters from B5 as are needed.

Nobody said it was going to be easy.

Missing Multiple Vaccinations

This question comes up every once in a while - if I have kids who are missing multiple vaccination records, how can I find them? To find the missing pertussis or MMR vaccinations is easy, but what about the kids missing ALL of them?

In other words, are ALL of these blank? That's what the AND function does; the ISBLANK function is checking the blank-ness of each cell.

Missing on Both the State and School Lists

In the Functions class, we had an example where the District list contained kids that were missing from both the state list and the district list.

So we got the #N/A error for those kids, individually. What if they're missing in both?

Assume the state MATCH function is in D2, the school MATCH function is in E2:
=IF(AND(ISNA(D2),ISNA(E2)),"Missing in both lists",IF(ISNA(D2),"Missing in State List",IF(ISNA(E2),"Missing in School List","Not Missing in Either")))

Check first if both D2 and E2 are showing the N/A error; that's what ISNA checks for. If that's the case, then show "Missing in both lists."

If that's not the case, then check if D2 has the #N/A error. If so, then display the error "Missing in State List."

If that's not the case, then check if E2 has the #N/A error. If so, then display the error "Missing in School List."

If none of these conditions have been satisfied, then the kid is in both lists. Display the message, "Not Missing in Either."

PivotTable Miscellaneous Wonders

Expand and Collapse the Data Source

If you use PivotTables, and you use Office 2003 or lower for the PC (2008 or older for the Mac), you'll want to pay attention to this next trick.

PivotTables in the older versions of Excel make it difficult to expand and contract the range of cells that you use to base the PivotTable upon.

If you know how to create a named range, though, you can improve your odds of success with this well-documented trick - the dynamic named range.

=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)) is the syntax.

There are two functions at work here - OFFSET and COUNTA. The OFFSET function says, "move over this many columns and this many rows, in a range this many columns wide and this many rows high." The COUNTA function you use to figure out how many columns wide and how many rows high the range is.

Give the range a name, like "data." When you start up the PivotTable Wizard, instead of having the source be "Sheet1!$A$1:$T$6567", the source is "data."

Copies of PivotTables

Remember, you can have one data source that drives multiple PivotTables. Take advantage of that. Setup the first one, then make copies of it. Dissassemble, re-assemble. Rinse, lather, repeat.

Stacking Row and Column Fields

The main reason to stack row and column fields is because you have groups and sub-groups, such as District and School. In addition, you can use two groups that are separate but related, such as gender and ethnicity, where one isn't contained in the other, but they intersect.

## Date and Time Miscellany

## How Many Days Have we Been in School?

In the Clean up and Analyze class, we talk a little about the difference between two dates - if I know the student's DOB, I can figure out how old they are; I can figure out whether or not they're eligible for Driver's Education, stuff like that.Question came up - what about how many days we've been in school? DATEDIF is not sufficient to handle that problem, because, of course, we're not in school on weekends, and DATEDIF counts weekdays and weekends equally.

So what can we do? Use NETWORKDAYS. When you read that function name, it's not "network days," it's "net work days." As in, what are the net number of days between start and end? The implication is, weekends are explicityl excluded, and it's possible to exclude holidays, as well.

How? =NETWORKDAYS(A1,B1,C1:C7) would assume the following:

The start day of school is in A1. The last day of school is in B1. C1:C7 stands for your holidays. Each one would have to be listed out individually, so if your spring break was say, March 15th through March 22nd, each would have to be in its own cell. But you can put in your snow days, there, as well.

## Age as a Decimal

In the Cleanup and Analyze class from Day 1, we talked about the DATEDIF function, but a question came up about how to do it as a decimal, that I never really got to answer. I want to take some time and answer it now.If we assume that .5 is one half of a year, and .75 is three-quarters of a year, then the DATEDIF function can do the job, with a little twist.

The first part is pretty straightforward: =DATEDIF(A1,NOW(),"y"), where A1 is the DOB, NOW() is today's date, and "y" is the increment.

Now, to add the month fraction: =DATEDIF(A1,NOW(),"y")+(DATEDIF(A1,NOW(),"ym")/12)

where the "ym" is the number of months left over after the years are calculated. You divide the remaining months by 12, to get the decimal portion. So if someone's birthday was August 28, 1970, and today's date was 2/28/2011, they would be 40.5 years old; 6 months until their birthday. It's a crude approximation, but it's effective.

## Text Functions and Wonders

## Locker Combinations, Part 2

What if you have already imported the locker combinations already, and you have to fix them? As you have probably found out by now, when you import locker combinations into Excel, they might get converted into dates; 01-15-24 might become January 15, 2024. Ugh.## Unique Usernames, Enforce The Six Character Limit

Of course, we all know about the 4-1-1 trick - if someone has a name like "Geoffrey G Lilley," then the matter of making a username is pretty easy; lillgg.What about Jeffrey Wu? That would be just wuj, if we accept the default.

Couple of challenges there - check the length of the last name, check the length of the first name, figure it out.

Here's one quick-and-dirty approach:

=LOWER(IF(LEN(A4)<4,A4&LEFT(B4,4-LEN(A4)))&C4)

In other words, if the length of A4 (the first name) is less than 4, then grab all of A4. Then, take however many characters you need from the first name to make it right, then add the middle initial.

## No middle initial?

=LOWER(IF(LEN(A5)<4,A5&IF(C5<>"",LEFT(B5,4-LEN(A5))&C5,LEFT(B5,6-LEN(A5)))))In other words, check A5 (the first name); if that's less then 4, check to see if C5 is blank. If C5 is NOT blank, then use B5, the number of characters needed from B5, then C5. If C5 is blank, use A5, then as many letters from B5 as are needed.

Nobody said it was going to be easy.

## Missing Multiple Vaccinations

This question comes up every once in a while - if I have kids who are missing multiple vaccination records, how can I find them? To find the missing pertussis or MMR vaccinations is easy, but what about the kids missing ALL of them?Try this:

=AND(ISBLANK(J2),ISBLANK(K2),ISBLANK(L2),ISBLANK(M2))

In other words, are ALL of these blank? That's what the AND function does; the ISBLANK function is checking the blank-ness of each cell.

## Missing on Both the State and School Lists

In the Functions class, we had an example where the District list contained kids that were missing from both the state list and the district list.So we got the #N/A error for those kids, individually. What if they're missing in both?

Assume the state MATCH function is in D2, the school MATCH function is in E2:

=IF(AND(ISNA(D2),ISNA(E2)),"Missing in both lists",IF(ISNA(D2),"Missing in State List",IF(ISNA(E2),"Missing in School List","Not Missing in Either")))

Check first if both D2 and E2 are showing the N/A error; that's what ISNA checks for. If that's the case, then show "Missing in both lists."

If that's not the case, then check if D2 has the #N/A error. If so, then display the error "Missing in State List."

If that's not the case, then check if E2 has the #N/A error. If so, then display the error "Missing in School List."

If none of these conditions have been satisfied, then the kid is in both lists. Display the message, "Not Missing in Either."

## PivotTable Miscellaneous Wonders

## Expand and Collapse the Data Source

If you use PivotTables, and you use Office 2003 or lower for the PC (2008 or older for the Mac), you'll want to pay attention to this next trick.PivotTables in the older versions of Excel make it difficult to expand and contract the range of cells that you use to base the PivotTable upon.

If you know how to create a named range, though, you can improve your odds of success with this well-documented trick - the dynamic named range.

=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)) is the syntax.

There are two functions at work here - OFFSET and COUNTA. The OFFSET function says, "move over this many columns and this many rows, in a range this many columns wide and this many rows high." The COUNTA function you use to figure out how many columns wide and how many rows high the range is.

Give the range a name, like "data." When you start up the PivotTable Wizard, instead of having the source be "Sheet1!$A$1:$T$6567", the source is "data."

## Copies of PivotTables

Remember, you can have one data source that drives multiple PivotTables. Take advantage of that. Setup the first one, then make copies of it. Dissassemble, re-assemble. Rinse, lather, repeat.## Stacking Row and Column Fields

The main reason to stack row and column fields is because you have groups and sub-groups, such as District and School. In addition, you can use two groups that are separate but related, such as gender and ethnicity, where one isn't contained in the other, but they intersect.