Create new dates every 3 weeks

[bishop7262]

I have 3 columns, Renewal Date, Last Charged, Next Charged...

Column A: Renewal Date 7/1

Column B: Last Charged 7/24

Column C: Next Charged 8/14

As you may noticed these dates are always 3 weeks apart. I want it to where on 8/14, Last Charged date is updated to 8/14 and Next Charged date is updated to 9/5

Is there a way to set this up automatically?

 

[villy]

As I understand:

Column A : 7/1
Column B :=C1 8/14
Column C :=B2+22 (for 3 weeks) 9/5

(drag as you like)

Hope this will help.
Open for comments thanks

 

[bishop7262]

Thanks for the input but this won't work. The dates in each column are not necessarily in chronological order:

Column A: 7/21, 8/12, 9/1, 9/5, 9/18

Column B is always 3 weeks from Column A

Column C is always 3 weeks from Column B

 

[Maheshp]

Hi
Assuming your date in column A cell A2 then you can use below formula
B2 '=DATE(YEAR(A2),MONTH(A2),DAY(A2)+21)
C2 '=DATE(YEAR(B2),MONTH(B2),DAY(B2)+21)

 

[bishop7262]

Works great but I need it to constantly update. Here is what I mean:

Column A: John Doe

Column B: 7/1

Column C: 7/22

Column D: 8/14

Well after 8/14 I need Column C to be updated to 8/14 and Column D updated to 9/5

Makes sense?

Thank you for your help

 

[yogeshmaney]

go reverse then

Assuming u vl update D2...

C2 '=DATE(YEAR(D2),MONTH(D2),DAY(D2)-21)
B2 '=DATE(YEAR(C2),MONTH(C2),DAY(C2)-21)
A2 '=DATE(YEAR(B2),MONTH(B2),DAY(B2)-21)

 

[bishop7262]

go reverse then

Assuming u vl update D2...

C2 '=DATE(YEAR(D2),MONTH(D2),DAY(D2)-21)
B2 '=DATE(YEAR(C2),MONTH(C2),DAY(C2)-21)
A2 '=DATE(YEAR(B2),MONTH(B2),DAY(B2)-21)

Thanks but I am confused on how to set this up with dates in D2

 

[yogeshmaney]

is D2 date linked to Current date or Today

 

[markmzz]

If I understand correctly what you want, so this can help you:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Renewal Date</td><td style="font-weight: bold;;">Last Charged</td><td style="font-weight: bold;;">Next Charged</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">7/1/2011</td><td style="text-align: right;;">7/22/2011</td><td style="text-align: right;;">8/12/2011</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">7/22/2011</td><td style="text-align: right;;">8/12/2011</td><td style="text-align: right;;">9/2/2011</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">8/12/2011</td><td style="text-align: right;;">9/2/2011</td><td style="text-align: right;;">9/23/2011</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">9/2/2011</td><td style="text-align: right;;">9/23/2011</td><td style="text-align: right;;">10/14/2011</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">9/23/2011</td><td style="text-align: right;;">10/14/2011</td><td style="text-align: right;;">11/4/2011</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">10/14/2011</td><td style="text-align: right;;">11/4/2011</td><td style="text-align: right;;">11/25/2011</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">11/4/2011</td><td style="text-align: right;;">11/25/2011</td><td style="text-align: right;;">12/16/2011</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">11/25/2011</td><td style="text-align: right;;">12/16/2011</td><td style="text-align: right;;">1/6/2012</td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Dates</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=B2+21</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=C2+21</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=B2+21</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=B3+21</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=C3+21</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[bishop7262]

is D2 date linked to Current date or Today

D2 would represent the future date