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.
Not quite what I had in mind 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. anyways, I develop an Idea to get this with 2 formulas even after trying hard. will try to get that in a formula. PS:- What's the last date?
hold Ctrl+Shift while hitting enter. unwanted space occurs in formula while copying, kindly accept the correction.
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
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
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
(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
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.
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.
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 )
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.