Centennial Weekend Birthdays

By in Date Functions on .

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 thoughts on “Centennial Weekend Birthdays

  1. Jeff Weir

    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

    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 :)

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax