Help with finding certain Dates

[Im_Offset]

Hello,

I'm unable to think of formulas to help me find certain dates. Please see the spreadsheet for the desired outcome.

Basically, I want to fill in a table based off of today's date. The table has three rows. The first row is to contain information about this month, while rows 2 and 3 will contain information about the next 2 months respectively.

There are 6 columns in the table. The first column is to name the appropriate month (i.e. January, February, etc). The next five columns are to list the 1st, 2d, 3d, 4th, and 5th Friday of that month in the mm/dd/yy format. If the month, only has 4 Fridays in it, then I want the cell for the 5th Friday to be "-"

Can someone teach me how to do that?

Thank you in advance!

 

[eibi]

First, I think you don't want Column C to return a month name only. You want it to return a real date, then you can change the formatting to display a month only.

In Cell C6, enter and drag down to fill C7 and C8:

=EOMONTH($D$1,ROWS($C$5:C5)-1)​

Then press Ctrl+1 (or right click and select Format Cells), select Number, Custom, and in the Type: box, replace with mmmm

Next, the formula you want in D6 is something like this:

=EOMONTH($C6,-1)+MATCH(1,N(IF(1,WEEKDAY(EOMONTH($C6,-1)+{1,2,3,4,5,6})=6)),0)​

-- There's probably a more elegant formula, but this one will do what you want. Note that this is an array formula, to be confirmed with Ctrl+Shift+Enter.

If you don't like an array formula, you can use this in D6 instead:

=EOMONTH($C6,-1)-WEEKDAY(EOMONTH($C6,-1))+IF(WEEKDAY(EOMONTH($C6,-1))>6,13,6)​

Drag down to fill D7 and D8.

Last, In E6, paste the following formula and drag to fill the rest of the cells:

=IF(MONTH(D6)=MONTH(D6+7),D6+7,"-")​

See attached.

 

[John Jairo V]

Hi @Im_Offset (and @eibi)

Check file, with the following formulas:

[C6]: =1+EOMONTH($D$1,ROWS(C$6:C6)-2); Custom Number Format mmmm
[D6]: =C6+6-WEEKDAY(C6)
[E6]: =IF(MONTH(D6+7)=MONTH(D6),D6+7,"-"); Drag it right.

All formulas Drag it down.

Blessings!

 

[eibi]

@John Jairo V

Super!

I knew I had over complicated D6.