Write Every Friday for the last 55 week, but need to exclude the good friday holiday

**Fortier40** · 09-16-2018, 03:56 PM

So Im looking to drag every friday from left to right using the formula workday but I need the formula to ignore the 3/30/2018 (good friday holliday) and change it to the last business day which is the 3/29/2018.

My formula is =WORKDAY(C2,-5,$A$1) A1 is the holliday date (3/30/2018)

Everything goes fine untill I come to the holliday, I get the 3/29/2018, which is what I want, but past this point I am getting every thuesday. I can't figure something that past this thuesday I still want to have the fridays.

Thanks for your time.

Alex.

**pjwhitfield** · 09-17-2018, 08:01 AM

What is the value of cell C2?

**Glenn Kennedy** · 09-17-2018, 08:03 AM

You could try this:

=WORKDAY(IF(AND(C2<$A$1,C2+7>$A$1),C2+1,C2),-5,$A$1)

in D2, and dragged across.

**Fortier40** · 09-20-2018, 08:41 PM

Originally Posted by Glenn Kennedy

You could try this:

=WORKDAY(IF(AND(C2<$A$1,C2+7>$A$1),C2+1,C2),-5,$A$1)

in D2, and dragged across.

Exactly what I need.

Thank you very much !

**Glenn Kennedy** · 09-21-2018, 01:48 AM

Did you include the correct quotation?

Whichever... You're welcome.

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

**Fortier40** · 01-23-2020, 09:26 PM

Im looking to enlarge the data range to 4 years, somehow I can't manage to copy/paste the actual formula to add the holidays of the past 4 years.
Hollidays.

Hollidays are;

4,10,2020 ON CELL A1
4,19,2019 ON CELL B1
3,30,2018 ON CELL C1
4,14,2017 ON CELL D1

C10 is the last friday that is not a holliday.

Actual Formula is
=WORKDAY(IF(AND(C10<$A$1,C10+7>$A$1),C10+1,C10),-5,$A$1)

Thank you very much for your time

**JeteMc** · 01-27-2020, 09:34 AM

Try pasting the following into cell D10 and drag across:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**Fortier40** · 02-01-2020, 10:35 AM

I think I didnt ask my question correctly and there was some error in the cell referencing. I joined an excel file which I think will explain better my question. Glenn Kennedy had the solution when I first asked for one holliday, now I simply want to add the good fridays of the last 4 years. I think working on the actual formula might be easier.

**JeteMc** · 02-01-2020, 01:36 PM

The attached copy of the file shows the formula (post #8) applied beginning with next Friday and extending back through the spring of 2017.
Let us know if you have any questions.

**Fortier40** · 02-02-2020, 11:13 AM

Thank you very much works, like a charm, but past the spring 2017 i dont have values and i need 61 more friday i guess its minor changes, but i cant figure it out. Thank you again

**AliGW** · 02-02-2020, 11:23 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

**Fortier40** · 02-02-2020, 06:03 PM

well it's partialy solved, it works but I need to extend the data for 61 more columns

**JeteMc** · 02-02-2020, 07:27 PM

Try the following with the file attached to post #9:
1. Place the date of Good Friday 2016 (3/25/2016) in cell E1
2. Modify the formula to read:

Formula:

Please Login or Register  to view this content.

3. Drag the formulas in D2:D3 over to column HI
Note that the formulas ignore Good Friday 2016 in column GW
Let us know if you have any questions.

**protonLeah** · 02-02-2020, 11:41 PM

If you don't have to use WORKDAY, you can try:
1. Get the next Friday after some date in A1 with:

Please Login or Register  to view this content.

1. Calculate previous Fridays and test for good friday with:

Please Login or Register  to view this content.

On a new sheet, create a named range of Good Friday dates (column A) with:

Please Login or Register  to view this content.

**Fortier40** · 02-03-2020, 08:22 PM

Originally Posted by JeteMc

Try the following with the file attached to post #9:
1. Place the date of Good Friday 2016 (3/25/2016) in cell E1
2. Modify the formula to read:

Formula:

Please Login or Register  to view this content.

3. Drag the formulas in D2:D3 over to column HI
Note that the formulas ignore Good Friday 2016 in column GW
Let us know if you have any questions.

Works fine. Thank you alot.

If I understand, the formula goes in error when we get in a year that does not match cells A1:E1. If thats the case, which it seems it's exactly what I was in need for, i fell like its really easy to manage.

Again, thank you for your time.

**Fortier40** · 02-03-2020, 08:28 PM

Originally Posted by protonLeah

If you don't have to use WORKDAY, you can try:
1. Get the next Friday after some date in A1 with:

Please Login or Register  to view this content.

1. Calculate previous Fridays and test for good friday with:

Please Login or Register  to view this content.

On a new sheet, create a named range of Good Friday dates (column A) with:

Please Login or Register  to view this content.

Hi Proton, i'll stick with the aggretate function formula. Somehow, does that formula works well with dragging accross ?

**JeteMc** · 02-03-2020, 10:22 PM

As to post #15:
Yes, the formula will return a #NUM! error if it gets to a year not found in A1:E1.
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

**FlameRetired** · 02-03-2020, 11:28 PM

Edit
Please see next post / attachment.
This one has an error in it. Messes up in mid 4/2019.

A somewhat different approach.

Formula is extended back 160 weeks to the first week of 2017. (overkill)

In D4 this formula filled to column FH:FH.

Formula:

Please Login or Register  to view this content.

That range is named All

In A7:D7 a helper range that establishes the last Friday of each Holiday month. It is named LastFridays Its formula is

Formula:

Please Login or Register  to view this content.

In A9 and A10 are some x-check formulas to test my sanity. This one matches the only Thursday in the All range.

Formula:

Please Login or Register  to view this content.

This one counts the Thursdays in the All range. There is only one as planned.

Formula:

Please Login or Register  to view this content.

**FlameRetired** · 02-04-2020, 01:26 AM

The named helper ranges remain the same as previously.

The new formula in D4 and filled across is

Formula:

Please Login or Register  to view this content.

**Fortier40** · 04-17-2021, 11:21 AM

Hello, the formula been working fore more than a year, but somehow I am having difficulties returning the last Fridays since the December 25th 2021 and January 1 2021 hollidays (Both are friday hollidays). Somehow when the formulas continues, it keeps returning the Wednesdays instead of Fridays. I wonder if it's due to the fact to we have two consecutive hollidays or it turns out that the January 1 holliday is buggin the whole thing.

I joined the excel file.

Thanks for your time

Alex.

**AliGW** · 04-17-2021, 11:42 AM

If you need further help, you might want to remove the solved tag.

Are you still using Excel 2010? If not, please update your profile. Thanks.

**Fortier40** · 04-17-2021, 04:04 PM

Thank you, I updated the thread as unsolved.

So I did a couple of test, and it seems like the formula start returning Wednesdays when we have two consecutives friday hollidays.

**JeteMc** · 04-17-2021, 10:49 PM

The formula in the file attached to post #20 is different from the one proposed in post #7 and used in the file attached to post #9, as it includes a function not recognized by the 2019 version of Excel (displayed as _xlfn.SINGLE).
The formula from Post #7 will still work to give the dates of Fridays except for Good Friday, given that the dates of Good Friday for the years 2017:2021 are placed in cell A1:D1.
Has the request has changed to include Christmas Days and New Year's Days that fall on Fridays?
Let us know if you have any questions.

**Fortier40** · 04-20-2021, 08:43 PM

Thank you for the reply.

I had to modify the formula a little as there was more Friday holiday during that stretch of time. It worked good until the December 25th 2020 and January 1st 2021. Initially the question should have been every holiday that is a Friday where the Stock market is closed = use the before business day. Somehow at that moment i didn't think I would come up with that problem.

Thanks for your time

Alex.

**JeteMc** · 04-21-2021, 10:14 AM

Try replacing the formula in cell D2 with:

Formula:

Please Login or Register  to view this content.

and then copy across.
Let us know if you have any questions.

Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Re: Write Every Friday for the last 55 week, but need to exclude the good friday holiday

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Find Monday & Friday of the last week

[SOLVED] How can I get a row to display the date of the last friday, and every friday before that?

Bookmarks

Bookmarks

Posting Permissions