Got Excel skills? Let's see. Support seems to be waning a little for these challenges, so next month (the Christmas Special!) will be the last Excel Challenge for a while. This month's challenge is a genuine problem that I need solving, and the winning solution will find its way into the real world Here are the usual rules. Create a single Excel formula to solve the stated problem. The shortest formula wins (when calculating the length of the formula there's no need to include the leading "=", or the curly braces that Excel uses to represent array formulae). The formula's number format can be set to whatever you want. Conditional formatting is not allowed. Problem #10 Assume that cell A1 contains the month number and A2 contains the year number of any date. The challenge is to create a single formula to return the day number of the first Monday in that month. A1 A2 6 1955 --> 6 1 2015 --> 5 9 2110 --> 1
slightly shorter: =MOD(10-MOD(DATE(A2,A1,1)+1,7),7)+1 Sad to hear that these will stop. I normally don't contribute as great solutions are posted so quickly!
A slight edit of this gives an even shorter solution: =8-WEEKDAY(DATE(A2,A1,1),12) Edit: Muppet beat me to it!
Not using the last argument of the DATE function does open up some interesting possibilities, and this solution would be excellent, if only it worked Unfortunately it gives an answer of Monday 8th May 2000, which of course should be Monday 1st May 2000. pma99car has worked out a fix for this, but it requires another MOD function. I'm still hoping for a compact MOD answer
Awesome That's definitely the sort of compact MOD answer I was after! Maybe you could explain how it works? The -2 in the DATE function returns the antepenultimate day of the preceding month, but it's not immediately obvious how that helps
What, and spoil the magic? Firstly, as numbers, dates that are congruent to 0 mod 7 are Saturdays in Excel. This goes up by one as we advance through the days of the week, and 'ticks over' from Friday to Saturday. Secondly, if the first day of the month is a Tuesday, then the first Monday of the month is the 7th. This date goes down by one as we advance through the days of the week, and 'ticks over' from Monday to Tuesday. Therefore we need to move the date from the first of the month, so that the 'tick over' occurs from Friday to Saturday. We can do this by counting back three days from the first of the month. This would be Code: DATE(A2,A1,1)-3 which is the same as Code: DATE(A2,A1,1-3) i.e. Code: DATE(A2,A1,-2) The range is going down, so we need the minus sign, and our answers mod 7 are in the range [-6,0], so we need to add 7 to get the range [1,7]. I've explained this very badly - it's much easier with a table, but I don't have time. Of course, you could go forward a week and get the same answer, so Code: 7-MOD(DATE(A2,A1,5),7) should also work, and is one character shorter.
