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.

2 Comments

  1. Jeff Weir says:

    I’m lucky…my birthday falls on a public holiday: April 25th, which is called ‘ANZAC Day’ in New Zealand. (And no, AC doesn’t stand for Air Conditioning)
    My wife is unlucky…her birthday falls on a hangover: 1 January.
    Well, that’s one of the reasons she’s unlucky. I’m sure you can imagine others…

  2. Bob Phillips says:

    Have you got too much time on your hands Dick?

    Why did you change the test logic for that min age, >= 6 works fine, and you don’t need to add the “” resultant

    =MIN(IF(WEEKDAY(DATE(YEAR(O16)+{20,30,40,50,60},MONTH(O16),DAY(O16)),2)>5,{20,30,40,50,60}))

    You could also use an array formula to get just the dates that return a weekend date in each age band

    =IFERROR(SMALL(
    IF(WEEKDAY(DATE(YEAR(ROW(INDIRECT(“1:”&TODAY())))+B$1,MONTH(ROW(INDIRECT(“1:”&TODAY()))),DAY(ROW(INDIRECT(“1:”&TODAY())))),2)>=6,
    DATE(YEAR(ROW(INDIRECT(“1:”&TODAY()))),MONTH(ROW(INDIRECT(“1:”&TODAY()))),DAY(ROW(INDIRECT(“1:”&TODAY()))))),ROW($A1)),””)

    although that can get a tad slow :)

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: