Auto fill dates based on another cells contents.

[The Jackal]

Hi All,

First time poster so please be gentle.

I am trying to get a spread sheet i have to auto complete a date based on another cells contents.


[TABLE="width: 254"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Frequency[/TD]
[TD]DUE[/TD]
[TD]Next[/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD="align: right"]13 May 2018[/TD]
[TD="align: right"]13 June 2018[/TD]
[/TR]
[TR]
[TD]PF[/TD]
[TD="align: right"]14 May 2018[/TD]
[TD="align: right"]28 May 2018[/TD]
[/TR]
[TR]
[TD]PW[/TD]
[TD="align: right"]15 May 2018[/TD]
[TD="align: right"]22 May 2018[/TD]
[/TR]
</tbody>[/TABLE]


What i would like to happen is as above. I would like my "NEXT" column to display the date based on the info in my "FREQUENCY" column and the "DUE" date. So........ if Frequency is "PM" i want "NEXT" to display the date 1 Month from the date in my "DUE" column. PF would be Per Fortnight and PW, Per Week.

I hope that makes sense and that someone can help.

Carl

 

[oldbrewer]

in C2 =if(a2="PM",b2+30.25,if(a2="PF",b2+14,b2+7)))

the 30.25 is an attempt to average month times

if this is very critical to you I can make it more accurate...

 

[The Jackal]

in C2 =if(a2="PM",b2+30.25,if(a2="PF",b2+14,b2+7)))

the 30.25 is an attempt to average month times

if this is very critical to you I can make it more accurate...

Thank you. The MONTH is quite key as i would like it to display the same date of each month.

Thanks again.

 

[The Jackal]

I had to remove one of the brackets at the end but this works. February will be a problem as will only have 28 days.

Also is there a way of this formula returning a blank if there is no date in the adjacent columns?

 

[oldbrewer]

[TABLE="width: 1082"]
<colgroup><col><col><col><col span="3"><col><col span="9"></colgroup><tbody>[TR]
[TD]Frequency[/TD]
[TD]DUE[/TD]
[TD]Next[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]month[/TD]
[TD]days[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD="align: right"]13-May-18[/TD]
[TD="align: right"]13-Jun-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]May[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PF[/TD]
[TD="align: right"]14-May-18[/TD]
[TD="align: right"]28-May-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]May[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PW[/TD]
[TD="align: right"]15-May-18[/TD]
[TD="align: right"]22-May-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]May[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]mytable[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD="align: right"]13-Feb-18[/TD]
[TD="align: right"]13-Mar-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Feb[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PF[/TD]
[TD="align: right"]14-Apr-18[/TD]
[TD="align: right"]28-Apr-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Apr[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PW[/TD]
[TD="align: right"]15-May-18[/TD]
[TD="align: right"]22-May-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]May[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD="align: right"]13-Oct-18[/TD]
[TD="align: right"]13-Nov-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Oct[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PF[/TD]
[TD="align: right"]14-Nov-18[/TD]
[TD="align: right"]28-Nov-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Nov[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PW[/TD]
[TD="align: right"]15-Dec-18[/TD]
[TD="align: right"]22-Dec-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dec[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]apr[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD="align: right"]13-Jan-18[/TD]
[TD="align: right"]13-Feb-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]aug[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD="align: right"]13-Mar-18[/TD]
[TD="align: right"]13-Apr-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mar[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]dec[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD="align: right"]13-Apr-18[/TD]
[TD="align: right"]13-May-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Apr[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]feb[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD="align: right"]13-May-18[/TD]
[TD="align: right"]13-Jun-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]May[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]jan[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD="align: right"]13-Jun-18[/TD]
[TD="align: right"]13-Jul-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jun[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]jul[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD="align: right"]13-Jul-18[/TD]
[TD="align: right"]13-Aug-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jul[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]jun[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD="align: right"]13-Aug-18[/TD]
[TD="align: right"]13-Sep-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Aug[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]mar[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD="align: right"]14-Sep-18[/TD]
[TD="align: right"]14-Oct-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sep[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]May[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD="align: right"]15-Nov-18[/TD]
[TD="align: right"]15-Dec-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Nov[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]nov[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD="align: right"]16-Dec-18[/TD]
[TD="align: right"]16-Jan-19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dec[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]oct[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD][/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][/TD]
[TD]sep[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 7"]it is perhaps illogical to have 7 day, 14 day and 28 or 30 or 31 day intervals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]the formula in C2 is[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"]=IF(A2="PM",B2+VLOOKUP(G2,mytable,3),IF(A2="PF",B2+14,B2+7))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"]cols G and H are helpers to determine days in a calendar month[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]every leap year you will have to change the 28 in mytable to 29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]the last column in mytable may not be necessary[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

you can add =if(a2="","",...........at start and add an extra close bracket at end