Excel Challenge #1

Discussion in 'Off-topic' started by Steve Hales, Feb 13, 2015.

  1. Steve Hales

    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: Feb 13, 2015
  2. Hemant Rupani

    Hemant Rupani Senior Member

    let A1 cell has date 13/2/2015

    Formula "=A1+28"

    even formula not general it is short. ;)
     
  3. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    Not quite what I had in mind :rolleyes:

    Question wording has been changed to clarify that the formula must always show the next occurrence.
     
  4. Oxymoron

    Oxymoron Ton up Member

    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
     
  5. Hemant Rupani

    Hemant Rupani Senior Member

    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?
     
  6. Hemant Rupani

    Hemant Rupani Senior Member

    hold Ctrl+Shift while hitting enter.
    unwanted space occurs in formula while copying, kindly accept the correction.
     
    Last edited: Feb 15, 2015
  7. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    Because it's called, err, Excel Challenge?

    Great logic in the solution though ;)
     
  8. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    Nice use of A1:A14 since 14 is the maximum number of months between Friday 13th's.
     
    Last edited: Feb 16, 2015
  9. didster

    didster Member

    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
     
  10. tiger

    tiger Ton up Member

    =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: Feb 16, 2015
  11. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    There's no +2 for {}, and since everyone will have to use an opening = there's no penalty for that either. So you're on a character count of 97, and I've got no idea how your formula works :confused:
     
  12. tiger

    tiger Ton up Member

    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
     
  13. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    (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!).
     
  14. tiger

    tiger Ton up Member

    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
     
  15. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    Lose the dollar signs to save another eight characters :D
     
  16. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    That's my 90cc formula beaten then!

    Code:
    =NOW()+MIN(IF(DAY(NOW()+ROW(1:999))=13,IF(WEEKDAY(NOW()+ROW(1:999))=6,ROW(1:999),999),999))
    Back to the drawing-board.
     
  17. Hemant Rupani

    Hemant Rupani Senior Member

    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. ;)
     
  18. Hemant Rupani

    Hemant Rupani Senior Member

    :cool:

    Code:
    =EOMONTH(NOW(),MATCH(1,WEEKDAY(EOMONTH(NOW(),ROW(A1:A15)-1)+1),0)-1)+13
     
  19. tiger

    tiger Ton up Member

    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 :) )
     
  20. Hemant Rupani

    Hemant Rupani Senior Member

    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.
     
  21. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    I guess that statement was more to set the context rather than form a specification of the question.
     

Share This Page