How do I change cell data based on anniversary date

O

only1zster

New Member
Joined
May 12, 2017
Messages
6
Folks,
I am trying to change data in a cell every year.

A2..............B2
45..............5/20/16

On 5/20/17 I want the data to go +5, but I want this to happen every year.


so far this is what I've tried
A2..........................................B2...........H2
=IF(TODAY()=B2, H2+5, H2)........5/20........45

This works once. I need A2 to increase by 5 every year on the date I put into B2. I added the hidden cell of H2 as the initial number (which will be different for other rows).

What am I missing? I've been looking at this too long and I know it has to be a simple fix.

Thanks in advance.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
[TABLE="width: 768"]
<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]2016[/TD]
[TD="width: 64, align: right"]11[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="align: right"]2017[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2018[/TD]
[TD="align: right"]21[/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]
[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]maybe populate B column with a lookup table output[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]so extract the year in text"yyyy" format[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]and multiply by 1 and use answer for lookup[/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]21[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2019[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]perhaps you will need to refer to today() function[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2020[/TD]
[TD="align: right"]31[/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]
[/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]
[/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]
[/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]
[/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]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
only1zster said:
Folks,
I am trying to change data in a cell every year.

A2..............B2
45..............5/20/16

On 5/20/17 I want the data to go +5, but I want this to happen every year.


so far this is what I've tried
A2..........................................B2...........H2
=IF(TODAY()=B2, H2+5, H2)........5/20........45

This works once. I need A2 to increase by 5 every year on the date I put into B2. I added the hidden cell of H2 as the initial number (which will be different for other rows).

What am I missing? I've been looking at this too long and I know it has to be a simple fix.

Thanks in advance.
Click to expand...



For clarification only the data in column A needs to advance +5 on the anniversary date.
 
Upvote 0
Cant say I really understand what you want here, but if you need a value to increase (by 5) for every year beyond a fixed year, then maybe something like...
=A1+(year(today())-fixed-year*5)
 
Upvote 0
FDibbins said:
Cant say I really understand what you want here, but if you need a value to increase (by 5) for every year beyond a fixed year, then maybe something like...
=A1+(year(today())-fixed-year*5)
Click to expand...


I need the values in Column A to increase by 5, on the anniversary of the date in Column B.

In my example A2 starts with 45, and the anniversary date is 5/20. On 5/20 next year, I want the value in A2 to be 50. The initial value in column A is not set. And the anniversary date I want to type in.
 
Upvote 0
OK maybe this?
[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][/tr]
[tr][td]
2​
[/td][td]
45​
[/td][td]
5/20/2014​
[/td][/tr]

[tr][td]
3​
[/td][td]
50​
[/td][td]
5/20/2015​
[/td][/tr]

[tr][td]
4​
[/td][td]
55​
[/td][td]
5/20/2016​
[/td][/tr]

[tr][td]
5​
[/td][td]
60​
[/td][td]
5/20/2017​
[/td][/tr]

[tr][td]
6​
[/td][td]
65​
[/td][td]
5/20/2018​
[/td][/tr]

[tr][td]
7​
[/td][td]
70​
[/td][td]
5/20/2019​
[/td][/tr]

[tr][td]
8​
[/td][td]
75​
[/td][td]
5/20/2020​
[/td][/tr]

[tr][td]
9​
[/td][td]
80​
[/td][td]
5/20/2021​
[/td][/tr]
[/table]
A3=IF(EDATE(B2,12)=B3,A2+5,A2)
copied down
 
Upvote 0
Here is my current data

[TABLE="class: grid, width: 128"]
<tbody>[TR]
[TD]a[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]1/1[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]5/1[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]11/1[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]1/1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]7/1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1/1[/TD]
[/TR]
[TR]
[TD]65[/TD]
[TD]4/1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1/1[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]9/1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]9/1[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]2/1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]7/1[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]1/1[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]4/1[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]4/1[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

So column A needs to increase by 5, on the date shown in column B.
 
Upvote 0
Assuming you have actual dates in column B. And you start a zero then

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]45
[/TD]
[TD]1/1/08
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]15
[/TD]
[TD]5/1/14
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]40
[/TD]
[TD]11/1/08
[/TD]
[/TR]
</tbody>[/TABLE]

In A1 try and copy down (with out the spaces around the <)
Code: 
=IF(TODAY() < DATE(YEAR(TODAY()),MONTH(B2),DAY(B2)),(YEAR(TODAY())-1-YEAR(B2))*5,(YEAR(TODAY())-YEAR(B2))*5)
 
Upvote 0
Scott T said:
Assuming you have actual dates in column B. And you start a zero then

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]45
[/TD]
[TD]1/1/08
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]15
[/TD]
[TD]5/1/14
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]40
[/TD]
[TD]11/1/08
[/TD]
[/TR]
</tbody>[/TABLE]

In A1 try and copy down (with out the spaces around the <)
Code: 
=IF(TODAY() < DATE(YEAR(TODAY()),MONTH(B2),DAY(B2)),(YEAR(TODAY())-1-YEAR(B2))*5,(YEAR(TODAY())-YEAR(B2))*5)
Click to expand...

Scott,
It looks like your adding 5 to the year. I need to have the value found in Column A increased by 5 every year.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
conditions for a userform to open
Replies
5
Views
205
still learning
S
J
  • Question Question
Anniversary Date Question - Excel
Replies
1
Views
350
awoohaw
awoohaw
S
  • Solved
Extract Date based on Dynamic Date
Replies
2
Views
188
Steve 1962
S
A
  • Solved
Conditional Formatting - apply rules to multiple rows based on specific cells (drop down menus)
Replies
4
Views
759
AndyB86
A
B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
189
sijpie
sijpie

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top