If date range is between December OR there is less than 25 business days before Good Friday, return Z

[pypyy]

Hi everyone,

I have been trying for the past hour to think of a solution for the below without success. I was wondering if anyone could help me.

• If Date of Meeting is any day during December, return the value = 25.
• If the Date of Meeting is less than 25 business days before Good Friday (30/03/2018), return the value = 25.
• Otherwise, display/return the value = 20.

Would anyone please help me?


Thank you.

 

[wideboydixon]

Would this work for you?

Book1
	A	B
1	Date Of Meeting	Return
2	19-Oct-2017	20
3	19-Nov-2017	20
4	19-Dec-2017	25
5	19-Jan-2018	20
6	19-Feb-2018	20
7	19-Mar-2018	25
Sheet1

Cell Formulas
Range	Formula
B2	=IF(OR(MONTH(A2)=12,AND(A2>DATE(2018,3,5),A2<=DATE(2018,3,30))),25,20)

WBD

 

[steve the fish]

Try this:

=IF(OR(AND(A1>WORKDAY(DATE(2018,3,30),-25),A1 < DATE(2018,3,30)),MONTH(A1)=12),25,20)<date(2018,3,30)),month(a1)=12),25,20)[ code]<date(2018,3,30)),month(a1)="12),25,20)<" html=""></date(2018,3,30)),month(a1)=12),25,20)[>

 

[pypyy]

Hi wideboydixon,

That looks good but for the second part(Good Friday), is there a way to return a value of 25 if the date of the meeting falls between 27/02/2018 to 30/03/2018?


Thanks and regards.

 

[wideboydixon]

Sorry I missed the "business days" piece. You could try Steve's solution or just adjust the date in my original formula:

=IF(OR(MONTH(A2)=12,AND(A2>=DATE(2018,2,27),A2<=DATE(2018,3,30))),25,20)

WBD

 

[pypyy]

Thanks to both of you so much.

This formula works nicely for me: =IF(OR(MONTH(A2)=12,AND(A2>=DATE(2018,2,27),A2<=DATE(2018,3,30))),25,20)

But along with having the Good Friday for 2018, is there a way to add 2019 and 2020 Good Friday in the formula as well?


Thanks so much in advance.

 

[wideboydixon]

Then it's time to break out the Easter Sunday formula which is OK for the next few years. I also think that 25 business days before 30-Mar-2018 is 23-Feb-2018 not 27-Feb-2018.

Book1
	A	B
1	Date Of Meeting	Return
2	19/10/2017	20
3	19/11/2017	20
4	19/12/2017	25
5	19/01/2018	20
6	19/02/2018	20
7	19/03/2018	25
Sheet1

Cell Formulas
Range	Formula
B2	=IF(OR(MONTH(A2)=12,AND(A2>=WORKDAY(FLOOR(DATE(YEAR(A2),5,DAY(MINUTE(YEAR(A2)/38)/2+56)),7)-36,-25),A2<=FLOOR(DATE(YEAR(A2),5,DAY(MINUTE(YEAR(A2)/38)/2+56)),7)-36)),25,20)

WBD