I need a Formula to Autofill the date based on the Date, Specific Value, and then no of days

[waseem0888]

I need the formula to fill out the date in (column C ) based on the date in (Column A) specific value in (Column B) and no of days to be fixed in the formula based on the value in Column B date from (ColumnA )and then fill the date in Column C

Example:

Column A (Issue Date) Column B(Category) Column C (Clouse Date)

1- Whenever (column B) category "CAR" is selected, Closeout Date (Column C) shall count 7 working days (Without Sat & Sunday) from the "Issued date" (Column A) and fill the date in (Column C)

2- Whenever (column B) category "AoA" is selected, Closeout Date (Column C) shall count 30 working days (Without Sat & Sunday) from the "Issued date" (Column A) and fill the date in (Column C)

3- Whenever (column B) category "External NC " is selected, Closeout Date (Column C) shall count 90 working days (Without Sat & Sunday) from the "Issued date" (Column A) and fill the date in (Column C)

4- Whenever (column B) category "External AoA " is selected, Closeout Date (Column C) shall count 150 working days (Without Sat & Sunday) from the "Issued date" (Column A) and fill the date in (Column C)

Thanks

 

[Peter_SSs]

Like this?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

24 03 22.xlsm
	A	B	C
1	Issue Date	Category	Closeout Date
2	Monday, 1 January 2024	CAR	Wednesday, 10 January 2024
3	Tuesday, 2 January 2024	AoA	Tuesday, 13 February 2024
4	Wednesday, 3 January 2024	External NC	Wednesday, 8 May 2024
5	Thursday, 4 January 2024	External AoA	Thursday, 1 August 2024
6	Friday, 5 January 2024	CAR	Tuesday, 16 January 2024
7	Saturday, 6 January 2024	CAR	Tuesday, 16 January 2024
8	Sunday, 7 January 2024	CAR	Tuesday, 16 January 2024
9	Monday, 8 January 2024	Other
10	Tuesday, 9 January 2024	External NC	Tuesday, 14 May 2024
waseem0888

Cell Formulas
Range	Formula
C2:C10	C2	=IFNA(WORKDAY(A2,CHOOSE(MATCH(B2,{"CAR","AoA","External NC","External AoA"},0),7,30,90,150)),"")

 

[Alex Blakenburg]

I tend to do that sort of thing using a lookup table. Its easier to understand and easier to maintain.
If you make the lookup table an actual table it will autoexpand so that you don't have to change the formula.

20240322 Working Days by Category waseem0888.xlsx
	A	B	C	D	E	F
1	Issue Date	Category	CloseOut Date		Lookup Table
2	22/03/2024	CAR	2/04/2024
3	22/03/2024	AoA	3/05/2024		Category	Working Days
4	22/03/2024	External NC	26/07/2024		CAR	7
5	22/03/2024	External AoA	18/10/2024		AoA	30
6					External NC	90
7					External AoA	150
Data

Cell Formulas
Range	Formula
C2:C5	C2	=WORKDAY(A2,VLOOKUP(B2,$E$4:$F$7,2,FALSE))

 

[waseem0888]

Thank you so much for the formula.


In the same way, I need to do one more thing what if I want to include weekends as well?

let's say in the category CAR is selected then it should count 30 days simply.

For Example:

Column A (Issue Date) Column B(Category) Column C (Clouse Date)
07-April-2024 CAR 07-May-2024

I have tried but have had no luck appreciate your help

Thanks

 

[Alex Blakenburg]

How is the formula going to decide whether you want working days or just days ?
Also your example will give the same result whether you use 30 days or 1 month because Apr is a 30 day month. Please confirm you want 30 days.

 

[waseem0888]

I want to count just days.

As in your example: 22/03/2024 for AoA and the closeout date is 3/5/2024 so it counts as 30 working days where I need to change in formula to count the total no of days like it should be 22/04/2024 it should count simply 30 days.

and the same way for other categories 7,90 and 150 days (not only working days)

 

[Alex Blakenburg]

Your 2nd example demonstrates exactly what I was trying to clarify. I think you will find that 22/03 to 22/04 is 31 days.

Also I don't know which formula you ended up using so below are both altered for the days instead of working days:
1) @Peter_SSs's

=IFNA(A2+CHOOSE(MATCH(B2,{"CAR","AoA","External NC","External AoA"},0),7,30,90,150),"")

2) Mine using a lookup table (I added the IFNA that Peter has in his)

=IFNA(A2+VLOOKUP(B2,$E$4:$F$7,2,FALSE),"")