• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Which year has Saturday 14th of June?

U

Ufoo

Member
Hello excel gurus, I got married on Saturday 14th of June 2008. I want to have a formula which finds out other years which have Saturday 14th of June, apart from the year 2008. I will appreciate your ideas.
 
Consider you have your date of married on cell A1

=TEXT(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)),"dddd, dd mmmm yyyy")
 
Thanks but what I want to do is to find years which have 14th of June as a Saturday. Let us say I want to do that within a span of 10 years from 2008. Thanks
 
Try this,

=INDEX(EDATE(--"14/6/2008",(ROW(A$1:A$100)-1)*12),AGGREGATE(15,6,ROW(A$1:A$100)/(MOD(EDATE(--"14/6/2008",(ROW(A$1:A$100)-1)*12),7)=0),ROWS($1:1)))

and, custom cells format to : "yyy"

formula copy down

Regards
 
Last edited:
Try This :
=DATE(YEAR(A$1)+1,MONTH(A$1),DAY(A$1))+WEEKDAY(A$1)-WEEKDAY(DATE(YEAR(A$1)+1,MONTH(A$1),DAY(A$1)))

Note : You can change +1 (Red highlighted change accordingly, if you want after two year (base on your date) change to +2
If you want past year then change to -1 or -2 or -3
 
bosco_yip said:
Try this,

=INDEX(EDATE(--"14/6/2008",(ROW(A$1:A$100)-1)*12),AGGREGATE(15,6,ROW(A$1:A$100)/(MOD(EDATE(--"14/6/2008",(ROW(A$1:A$100)-1)*12),7)=0),ROWS($1:1)))

and, custom cells format to : "yyy"

formula copy down

Regards
Click to expand...
Thanks a lot. Let me see if the formula works
 
Hi to all!

Another option could be:
=EDATE(--"14-6-2008",12*MATCH(7,INDEX(WEEKDAY(EDATE(A1,12*ROW(1:99))),),))

This formulae get the next date.

If you want a list of next Sat 14, try this:

=INDEX(EDATE(--"14-6-2008",12*ROW($1:$99)),AGGREGATE(15,6,ROW($1:$99)/(WEEKDAY(EDATE(--"14-6-2008",12*ROW($1:$99)))=7),ROWS($1:1)))

If you want only the year, could format "yyy" (or "yyyy"). You could use the YEAR function too.

Blessings!
 
Last edited:
r2c2 said:
Also, this array formula...

=2008+MATCH(7,WEEKDAY(DATE(2008+ROW(A1:A14),6,14)),0)
Click to expand...
Thanks a lot @r2c2. I have understood the formula. But I have a small question: how did you decide to have ROW(A1:A14)? What is your rationale? Thanks again
 
No rocket science. Just eye balling the "June 14 is Saturday" years over the next 2 decades, it seems the maximum gap is 12 years. So 14 seems like a safe number to use.
 
@Ufoo Based on my little simulation, the gap will always be 11 years or lesser (except for 29th of February, which can have same day of week in a repeating pattern of 28, 28, 28, 12 years)
 
r2c2 said:
Also, this array formula...

=2008+MATCH(7,WEEKDAY(DATE(2008+ROW(A1:A14),6,14)),0)
Click to expand...
Thanks @r2c2. Based on your assistance I have come up with 2 variations:
=SMALL(IF(WEEKDAY(DATE(2008+ROW($A$1:$A$50),6,14))=7,2008+ROW($A$1:$A$50),""),ROW())

=AGGREGATE(15,6,(2008+ROW($A$1:$A$50))/(WEEKDAY(DATE(2008+ROW($A$1:$A$50),6,14))=7),ROW(A1))
 
If you run this short VBA macro:
Code: 
Sub WhichYear()
    Dim i As Long, j As Long
    j = 1
    For i = 2008 To 9999
        If Format(DateSerial(i, 6, 14), "ddd") = "Sat" Then
            Cells(j, 1).Value = i
            j = j + 1
        End If
    Next i
End Sub

You will get a list of all years from 2008 to 9999 in which June 14th falls on a Saturday.
 
You must log in or register to reply here.
Back
Top