Formula to get date for every FRIDAY for the year

[rizmomin]

Hello:


Say Cell A1=2020

I need formula in cell A5 down to give dates of Evry FRIDAY
and 1st date of the month

In Attached file, i have shown the sequence

Please let me know if you have any questions.
Thanks.

Riz

[AliGW]

Please attach an .xlsx file, not .xlsb. Thanks.

[rizmomin]

Hello:

Attached is .xlsx file

Please let me know if you have any questions.
Thanks.

Riz

[AliGW]

Try this in A6 copied down:

=MIN(EOMONTH(A5,0)+1,WORKDAY.INTL(A5,1,"1111011"))

You could use this in A5:

=DATE(A1,1,1)

[oeldere]

Deleted (can't post the solution)

[6StringJazzer]

Deleted (can't post the solution)

We have a rather aggressive filter for HTML injection, and it often rejects benign formulas that contain a < or > sign. There are a couple of workarounds.

• Include a space after the < sign. My post was accepted just as you see it here.
• Highlight the < sign and then use the color button to color it black. It will still look the same but the filter will see the color codes around it and accept it.

[oeldere]

e5 = 01-01-2020

e6 =E5+5-(WEEKDAY(E5,2)) since 5 is Friday

e7 =IF(MONTH(E6) < MONTH(E6+7),EOMONTH(E6,0)+1,IF(EOMONTH(E5,0)+1=(E5+7),E5+14,IF(MONTH(E6)=MONTH(E5),E6+7,E5+7))) and drag down.


Where do you this kind of structor for?
Can you explain that to me.

[AliGW]

Why three formulae when it can be done with just two (see my solution above)?

[oeldere]

@AliGW,

1 formula is not always better, since it could be hard to read.

In this case your solution is a lot easier than mine.

Since I had made the formula, I feel the need to post.

Great job, Ali.

[Bo_Ry]

@Ali, Very clever formula
A small adjustment from Ali's formula to 1 formula at A5

=IF(A4,MIN(EOMONTH(A4,0)+1,WORKDAY.INTL(A4,1,"1111011")),DATE($A$1,1,1))

[AliGW]

Some nice options there, chaps.

Wonder what Riz will go for?

[Rick Rothstein]

@Bo_Ry,

I like Ali's solution as she wrote it instead. You know only one cell will meet the FALSE part of your IF statement, and you know which cell it is, so why burden every cell that follows it with the extra IF function call when they can only meet the TRUE part of that IF function call?

[rizmomin]

Hello All;
Great solution by Ali.
Superb...

Thanks to all.

Riz

[AliGW]

Thanks, Riz, and glad to help.

[oeldere]

Thanks for the rep.

Glad I could help.

Thanks for marking the question solved.