IF formula with Workday condition

[horizonflame]

Hi,

In Column A I have dates, and in Column B I have text ‘LARGE’ or SMALL’.

In Column C I want to reference the date from Column A with the condition that if the text in Column B is ‘SMALL’ then it adds 60 workdays taking into account a holiday range in Column D cells D1:D7. If the text is ‘LARGE’ then the date is reference as stated.

Many thanks for help with the formula. ?

 

[Eric W]

How about:

Book1
	A	B	C	D
1	Date	Size	New Date	1/1/2019
2	5/4/2019	Large	5/4/2019	1/27/2019
3	2/2/2019	Small	4/26/2019	5/27/2019
4	6/15/2019	Small	9/10/2019	7/4/2019
5	10/1/2019	Small	12/26/2019	9/2/2019
6	12/15/2019	Small	3/9/2020	11/28/2019
7	12/15/2019	Large	12/15/2019	12/25/2019
Sheet9

Cell Formulas
Range	Formula
C2	=IF(B2="Large",A2,WORKDAY(A2,60,$D$1:$D$7))

 

[horizonflame]

Hi Eric, thank you for your help. My criteria is more complex that first thought. Can you help with modifying the below please?


[TABLE="width: 219"]
<tbody>[TR]
[TD="width: 75, bgcolor: transparent"]Date
[/TD]
[TD="width: 64, bgcolor: transparent"]Size
[/TD]
[TD="width: 79, bgcolor: transparent"]Team
[/TD]
[TD="width: 75, bgcolor: transparent"]Hols
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]01/04/2019
[/TD]
[TD="bgcolor: transparent"]Large
[/TD]
[TD="bgcolor: transparent"]Team 1
[/TD]
[TD="bgcolor: transparent, align: right"]01/01/2019
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]01/06/2019
[/TD]
[TD="bgcolor: transparent"]Small
[/TD]
[TD="bgcolor: transparent"]Team 2
[/TD]
[TD="bgcolor: transparent, align: right"]26/08/2019
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]01/08/2019
[/TD]
[TD="bgcolor: transparent"]Large
[/TD]
[TD="bgcolor: transparent"]Team 3
[/TD]
[TD="bgcolor: transparent, align: right"]25/12/2019
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]01/10/2019
[/TD]
[TD="bgcolor: transparent"]Small
[/TD]
[TD="bgcolor: transparent"]Team 4
[/TD]
[TD="bgcolor: transparent, align: right"]26/12/2019
[/TD]
[/TR]
</tbody>[/TABLE]

IF OR Criteria:

1) Large <ref value="" current="">--- ref current value only
2) Small AND Team 1 or Small AND Team 2<ref days="" +60=""> --- ref current value +60 days
3) Small AND not Team 1 or Team 2 (any other cell value) --- ref current value only<ref value="" current="">

Many thanks!</ref></ref></ref>

 

[Eric W]

Try:

Book1
	A	B	C	D	E
1	Date	Size	Team	New Date	Holidays
2	4/1/2019	Large	Team 1	4/1/2019	1/1/2019
3	6/1/2019	Small	Team 2	8/26/2019	1/27/2019
4	8/1/2019	Large	Team 3	8/1/2019	5/27/2019
5	10/1/2019	Small	Team 4	10/1/2019	7/4/2019
6					9/2/2019
7					11/28/2019
8					12/25/2019
Sheet9

Cell Formulas
Range	Formula
D2	=IF(AND(B2="small",OR(C2={"Team 1","Team 2"})),WORKDAY(A2,60,$E$2:$E$8),A2)