• We are pleased to announce that the winner of our Feedback Prize Draw for the Winter 2024-25 session and winning £150 of gift vouchers is Zhao Liang Tay. Congratulations to Zhao Liang. If you fancy winning £150 worth of gift vouchers (from a major UK store) for the Summer 2025 exam sitting for just a few minutes of your time throughout the session, please see our website at https://www.acted.co.uk/further-info.html?pat=feedback#feedback-prize for more information on how you can make sure your name is included in the draw at the end of the session.
  • Please be advised that the SP1, SP5 and SP7 X1 deadline is the 14th July and not the 17th June as first stated. Please accept out apologies for any confusion caused.

Excel Challenge #10

S

Steve Hales

ActEd Tutor
Staff member
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
 
OK, this time even I can do this in excel:
=8-WEEKDAY(VALUE(CONCATENATE(A1,"-1-",A2)),12)
 
Last edited:
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!
 
Last edited by a moderator:
Oxymoron said:
OK, this time even I can do this in excel:
=8-WEEKDAY(VALUE(CONCATENATE(A1,"-1-",A2)),12)
Click to expand...

A slight edit of this gives an even shorter solution:

=8-WEEKDAY(DATE(A2,A1,1),12)

Edit: Muppet beat me to it!
 
Hemant Rupani said:
:D So, the count down to 23 :cool:
Code: 
8-MOD(DATE(A2,A1,)-1,7)
Click to expand...

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 :D 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 :)
 
Steve Hales said:
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 :D 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 :)
Click to expand...

I haven't fully checked this, but wouldn't

=7-MOD(DATE(B5,A5,-2),7)

fix this?
 
Bodhisattva said:
=7-MOD(DATE(B5,A5,-2),7)
Click to expand...

Awesome :D 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 :confused:
 
Steve Hales said:
Awesome :D 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 :confused:
Click to expand...

What, and spoil the magic? :p

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.
 
You must log in or register to reply here.
Back
Top