Adding a date based on another cell value

[Skyhaze001]

Hi, I have a spreadsheet that tracks internal quality issues with the following key cells:

Column A - date identified
Column G - Critical/Major/Minor
Column J - target close out date

Is it possible to auto-populate J with a date depending on the option selected in G? For example the target close out for a critical issue is 7 days after being identified so J should be A+7 days. Major should be A+14 days and minor should be A+30 days

 

[Candyman8019]

You could try:
=IF(G16="Critical",A16+7,IF(G16="Major",A16+14,IF(G16="Minor",A16+30)))

 

[Candyman8019]

If nothing has been entered in Column G, you could use this formula to keep things looking clean:
=IF(IF(G16="Critical",A16+7,IF(G16="Major",A16+14,IF(G16="Minor",A16+30)))=FALSE,"",IF(G16="Critical",A16+7,IF(G16="Major",A16+14,IF(G16="Minor",A16+30))))

 

[Toadstool]

Hi Skyehaze001,

This should do what you ask.

Skyehaze001.xlsx
	A						G			J
1	Date Identified						Critical /Major /Minor			Target Closeout Date
2	01-Jan-21						Critical			08-Jan-21
3	07-Feb-21						Major			21-Feb-21
4	16-Mar-21						Minor			15-Apr-21
5	22-Apr-21						Critical			29-Apr-21
6	29-May-21						Major			12-Jun-21
7	05-Jul-21						Minor			04-Aug-21
8	11-Aug-21						Critical			18-Aug-21
9	17-Sep-21						Major			01-Oct-21
10	17-Sep-21						Minor			17-Oct-21
11
Sheet1

Cell Formulas
Range	Formula
J2:J11	J2	=IFERROR(A2+CHOOSE(MATCH(G2,{"Critical","Major","Minor"},0),7,14,30),"")

 

[Fluff]

Another option

=IF(G2="","",A2+(IF(G2="Critical",7,IF(G2="Major",14,30))))

 

[Candyman8019]

Hi Skyehaze001,

This should do what you ask.

Skyehaze001.xlsx
	A						G			J
1	Date Identified						Critical /Major /Minor			Target Closeout Date
2	01-Jan-21						Critical			08-Jan-21
3	07-Feb-21						Major			21-Feb-21
4	16-Mar-21						Minor			15-Apr-21
5	22-Apr-21						Critical			29-Apr-21
6	29-May-21						Major			12-Jun-21
7	05-Jul-21						Minor			04-Aug-21
8	11-Aug-21						Critical			18-Aug-21
9	17-Sep-21						Major			01-Oct-21
10	17-Sep-21						Minor			17-Oct-21
11
Sheet1

Cell Formulas
Range	Formula
J2:J11	J2	=IFERROR(A2+CHOOSE(MATCH(G2,{"Critical","Major","Minor"},0),7,14,30),"")

Oh, I like that CHOOSE/MATCH combination!

 

[Skyhaze001]

Thank you all! For some reason most of them just left the cells blank even after I updated the cell reference to the correct row but one of them did work.

 

[Toadstool]

Thank you all! For some reason most of them just left the cells blank even after I updated the cell reference to the correct row but one of them did work.

You're welcome.
For my formula if the date is not a real date or the column G does not contain the text Critical, Major or Minor then it will return a blank. If you want to provide direction on what to do in those cases then we can cater for them.