• 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 #1

S

Steve Hales

ActEd Tutor
Staff member
You've got Excel skills? Let's see.

This is the first in a series of monthly Excel challenges. The rules are simple:
  • the submission must solve the stated problem
  • the solution must be limited to a single formula (clearly not CA2-compliant)
  • the shortest formula wins.
Problem #1

Today is Friday 13th. Construct a formula to always show the next Friday that falls on the 13th day of the month.
 
Last edited:
Not quite what I had in mind :rolleyes:

Question wording has been changed to clarify that the formula must always show the next occurrence.
 
Why only excel?

MATLAB answer:
datestr(datenum(date) + min((find((weekday(datenum(date) + (1:10000))==6).*(day(datenum(date) + (1:10000))==13)))))

ans =

13-Mar-2015
 
before alteration I thought my answer was the shortest. :cool:

anyways, I develop an Idea to get this with 2 formulas even after trying hard. will try to get that in a formula.:D

PS:- What's the last date?
 
=DATE(YEAR(NOW()),MATCH(TRUE,WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW())+ROW(A1:A14), 13))=6,0)+MONTH(NOW()),13)
Click to expand...
hold Ctrl+Shift while hitting enter.
unwanted space occurs in formula while copying, kindly accept the correction.
 
Last edited:
Hemant Rupani said:
hold Ctrl+Shift while hitting enter.
unwanted space occurs in formula while copying, kindly accept the correction.
Click to expand...

Well done.
Problem statement isn't clear:
  • If formula only needs to work on one Friday 13 to get the next, Hemant's solution is pretty good.
  • If it needs to work on any random day, it needs a tad bit more work to cover edge cases, eg if you run it on 2 Mar 2015 it won't give 13 Mar 2015
 
=42048+(MATCH(13,DAY(42048+ROW(OFFSET($A$1,(NOW()-42048)/7,1,200,1))*7),0)+ROUNDDOWN((NOW()-42048)/7,0))*7

Another CSE function.
a little longer at 106 chars (+2 for {} ?)
but should give :
12/3/2015 = 13/3/2015
13/3/2015 = 13/11/2015

EDIT:
replace rounddown by INT brings it to 98!
=42048+(MATCH(13,DAY(42048+ROW(OFFSET($A$1,(NOW()-42048)/7,1,200,1))*7),0)+INT((NOW()-42048)/7))*7
 
Last edited by a moderator:
42048 = 13 Feb 2015, a starting Fri 13th.
Any subsequent Fri 13th will be a multiple of 7 days later.
42048 + row(a1:a200)*7 will give me the date of the next 200 Fridays as an array.
I'm looking for the first one where the DAY() of the month is 13.
Some added complications with NOW() to give me the first one after today, and not after my starting date of 13th Feb.

Should probably use 99999 rows or something instead of 200 to future-proof it a bit better
 
tiger said:
(1) Any subsequent Fri 13th will be a multiple of 7 days later.

(2) Should probably use 99999 rows or something instead of 200 to future-proof it a bit better
Click to expand...

(1) That's a very clever way of approaching it.

(2) I don't think you'll need the next 99999 Fridays, just the next 99 would cover it (and it will reduce your current character count by one!).
 
slightly more elegant, and down to 89
=MATCH(1,(DAY(42048+ROW($A$1:$A$999)*7)=13)*((42048+ROW($A$1:$A$999)*7)>NOW()),0)*7+42048

These excel challenges are a bad idea
 
didster said:
Well done.
Problem statement isn't clear:
  • If formula only needs to work on one Friday 13 to get the next, Hemant's solution is pretty good.
  • If it needs to work on any random day, it needs a tad bit more work to cover edge cases, eg if you run it on 2 Mar 2015 it won't give 13 Mar 2015
Click to expand...

I altered it to get 1st day of Month on Sunday, then added 13 days
let D1 be the date.
=DATE(YEAR(D1),MATCH(1,WEEKDAY(DATE(YEAR(D1),MONTH(D1)+ROW(A1:A14)-1,1)),0)+MONTH(D1)-1,13)

Now, if you run it on 2 Mar 2015 it will give 13 Mar 2015,
but if D1=18-Mar-2015 it still gives 13-Mar-2015 or if D1=22-Feb-2015 it gives 13-Feb-2015
as ques is, "Today is Friday 13th. Construct a formula to always show the next Friday that falls on the 13th day of the month." so I don't wanna alter it. ;)
 
Steve Hales said:
Lose the dollar signs to save another eight characters :D
Click to expand...

actually I can:
- use the whole col A:A
- mod(42048,7) is 6 so look for Sat 7th

=MATCH(1,(DAY(ROW(A:A)*7)=7)*((ROW(A:A)*7)>NOW()-6),0)*7+6

Performance is c**p but 57 chars without = {}

(Gym is good for excel :) )
 
Steve Hales said:
Today is Friday 13th. Construct a formula to always show the next Friday that falls on the 13th day of the month.
Click to expand...

As ques. assumes Today is a Friday on 13th.
Let the cell A1=any date which has Friday on 13th day of a month, say 13/2/15

then
Code: 
=EDATE(A1,MATCH(6,WEEKDAY(EDATE(A1,ROW(A:A))),0))
always show the next Friday that falls on the 13th day of the month.
 
You must log in or register to reply here.
Back
Top