# Centennial Weekend Birthdays

On this episode of the BBC’s More or Less podcast, they discussed big, round birthdays that fall on a weekend. A listener said that she had to wait until her 60th birthday for it to fall on a weekend. The guy who figured out how unlucky she was tested every birthday from January 1, 1900. Since he picked that date, I assume he used Excel, but he never said.

They did include the caveat “as an adult” so that leaves off the 10th birthday. Here’s how I did the math. I started with 1/1/1900 is cell A2 and used the formula

`=A2+1`

copied down to today. Then in B1:J1, I entered the values 20-100. The formula in B2 is

`=WEEKDAY(DATE(YEAR(\$A2)+B\$1,MONTH(\$A2),DAY(\$A2)),2)>=6`

I added the value in row 1 to the year to make the centennial birthday and fed that into the WEEKDAY function. WEEKDAY returns 1 through 7 representing the day of the week. I used ‘2’ for the second argument so that Monday is 1 and Saturday is 6. Then I return TRUE or FALSE depending on whether the weekday is greater than or equal to 6.

Column K finds the minimum age that has a TRUE under it

`=MIN(IF(B2:J2,\$B\$1:\$J\$1,""))`

That’s an array formula, so I entered it with Ctrl+Shift+Enter. Next, I repeated 20-100 in column N. These formulas complete the table

``` O3 =COUNTIF(\$K\$2:\$K\$41832,N3) P3 =O3/SUM(\$O\$3:\$O\$11) Q3 =Q2+P3```

As if that wasn’t enough, I wanted to make a single formula that could accept a date and return the earliest major birthday that was on a weekend.

`=MIN(IF(WEEKDAY(DATE(YEAR(O16)+{20,30,40,50,60},MONTH(O16),DAY(O16)),2)<6,"",{20,30,40,50,60}))`

That's also an array formula, so you know what to do. I celebrated my 30th birthday on a weekend.

# Fixing Exported Dates

If you export a Profit and Loss statement from Quickbooks to a CSV file, you get dates that look like this: Excel turns them into this. January 2005 becomes January 5th of the current year. February 2005 becomes February 5th. That’s no good. In a spare row, I used this formula

`=DATE(DAY(A1)+2000,MONTH(A1)+1,0)`

to create the date I wanted. Then I copied and pasted special – values over the top of the original dates. It adds 2000 the day to get the correct year. Adding 1 to the month and taking the zeroth day will get you the last day of the month.

January 5, 2012 becomes January 31, 2005.