Changing the date based on conditions

Powellexcel

New Member
Joined
Dec 2, 2016
Messages
28
I have a 'Date Received' column in column H3 toH50, and a 'deadline date' column in column J3 to J50. I will always manually input the 'Date Received', but when I select an option from a dropdown in column C3 to C50, such as 'briefing task'. I want that input of 'briefing task' in column C, to add on 9 days to the 'deadline date' column J from the date it was received (column H).

Is this possible?
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,481
Platform
  1. MacOS
Welcome to the forum.

Is the "Deadline Date" an input field or a formula?
 

Powellexcel

New Member
Joined
Dec 2, 2016
Messages
28
The 'deadline date' in an input field. But I want it to have a formula so I do not have to manually put in how many days till deadline (if that makes sense?) So if I select 'briefings' in the task column then 9 days are added onto the received date but displayed in the 'deadline date' column
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,481
Platform
  1. MacOS
If you want it in the same cell as it's input, you can't do it with a formula, maybe with VBA which I can't help with.

Alternative is to add another column with the formula in.
 

Powellexcel

New Member
Joined
Dec 2, 2016
Messages
28

ADVERTISEMENT

Misunderstanding, I don't want the deadline date in the same cell as the received date, I just want the deadline date to have 9 days more to whatever the date is in 'date received' column based on me choosing 'briefing task' in column C3 to C50
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,481
Platform
  1. MacOS
That wasn't clear :)

What date do you want in Deadline date if C is not Briefing?
 

Powellexcel

New Member
Joined
Dec 2, 2016
Messages
28

ADVERTISEMENT

Sorry, if it's not briefing, it will be 'correspondance' / 'PQ' / 'OPQ' and so on there are a few others
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,481
Platform
  1. MacOS
You need to explain a bit more! If C3 = Briefing then you want H3+9days. What do you want if C3=the others?
 

Powellexcel

New Member
Joined
Dec 2, 2016
Messages
28
Column C3:C50 can has a dropdown that can have the following selected, the bracketed time is how long they will take - correspondence (5 days) | briefing (9 days) | PQ (2 days) | OPQ (1 day) | TO (4 days)

In column H (date received) is when the task is stated, for example 01/12/2016. So, If Correspondence is selected from column C, there will be no change to the date received, that will stay at 01/12/2016/ but because of the chosen one in column C, I want that to add 5 days added onto the date of the date received in the deadline date
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,481
Platform
  1. MacOS
Ok, create a small table as on the right of the example below, it can be anywhere.

Code:
[TABLE="width: 845"]
<!--StartFragment--> <colgroup><col width="65" span="13" style="width:65pt"> </colgroup><tbody>[TR]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"]Table[/TD]
  [TD="width: 65"][/TD]
 [/TR]
 [TR]
  [TD]opq[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD="class: xl63, align: right"]24/12/16[/TD]
  [TD][/TD]
  [TD="class: xl63, align: right"]25/12/16[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD]briefing[/TD]
  [TD="align: right"]9[/TD]
 [/TR]
 [TR]
  [TD]to[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD="class: xl63, align: right"]24/12/16[/TD]
  [TD][/TD]
  [TD="class: xl63, align: right"]28/12/16[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD]correspondance[/TD]
  [TD="align: right"]5[/TD]
 [/TR]
 [TR]
  [TD]pq[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD="class: xl63, align: right"]24/12/16[/TD]
  [TD][/TD]
  [TD="class: xl63, align: right"]26/12/16[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD]OPQ[/TD]
  [TD="align: right"]1[/TD]
 [/TR]
 [TR]
  [TD]briefing[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD="class: xl63, align: right"]24/12/16[/TD]
  [TD][/TD]
  [TD="class: xl63, align: right"]02/01/17[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD]PQ[/TD]
  [TD="align: right"]2[/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD]TO[/TD]
  [TD="align: right"]4[/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]

In J3 enter

=IFERROR(H3+LOOKUP(C3,$N$3:$O$7),H3)

Note : $N$3:$O$7 is the table, change it to the range where your table is.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,133,532
Messages
5,659,371
Members
418,499
Latest member
mbcmel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top