Type 13/2/2015 in C1, 13/3/2015 in C2 and so on... then drag on to some 100 cells. I am getting all the 13ths as input. In the next column D1, type WEEKDAY(C1) and double click to make the formula work for all cells down. Friday returns 6. So, in the next column E1, use the function IF(D1=6, 1, 0). Now you can filter 1s to see when Friday falls on 13th for 100 years.
I wonder if we're getting towards the limit of what's possible? tiger's amazing submission can be tweaked by removing the pair of brackets surrounding the second ROW(A:A)*7. That takes the character count down to an amazing 55
now it adds days rather than months..... Code: =NOW()+MATCH("13.Fri",TEXT(NOW()+ROW(A:A),"dd.ddd"),0)
Nice! I think you can get rid of a d, bringing it down to 52? Code: NOW()+MATCH("13.Fri",TEXT(NOW()+ROW(A:A),"d.ddd"),0)
I've just realised that the MATCH function can handle wildcard characters, so "13.Fri" can be reduced to "13.F*". This gets the count down to 51. But then "13*i" is even better still, and puts the current character count at 50. Code: NOW()+MATCH("13*i",TEXT(NOW()+ROW(A:A),"d.ddd"),0) Can anyone go sub-50?
There haven't been any new suggestions for a couple of days, so it looks as though Hemant is this month's winner - well done! Great application of the TEXT function, and after a couple of refinements the character count stands at 50. Watch out for Excel Challenge #2 - launching Friday 13th March 2015.
