# If + adding date values in many cells

#### Nalim

##### New Member
Hello, I'm struggling with this problem for an hours. Banging my head to the wall

In one column I have values 1,2,3 marking the years. Column N
In second I have a certain date (mm/dd/yyyy format). Column O

I want to have in column P added new date that will be one year after the certain date in column O if a certain condition is meet.

So I am using if function and tried with 3 different methods and formulas

=IF(N3>1,SUM(O3+365),0)
=IF(N3>1,O3+365,0)
=IF(N3>1,DATE(YEAR(O3)+1,MONTH(O3),DAY(O3)),0)

On the first cell it gives me an exact value, but when I click to drag over next cells - it gives me the same values like in the one in the first cell. I even tried naming the date range and still when I drag over to populate the cells bellow it stays the same.

Strange is that once I go to the other wanted cell, the formula shows correct values =IF(N134>1,SUM(O134+365),0) but the result is the one like from the start.
What is even more stranger is that when I click on some of those incorrect cells, and do not change the formula and press enter then it gives me a proper result.

When I'm dragging I even clicked all the possible options, fill, copy etc... no results.

### Excel Facts

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

#### JLGWhiz

##### Well-known Member
maybe

Code:
``=IF([COLOR=#B22222]\$N\$3[/COLOR]>1,O3+365,0)``

#### Tetra201

##### MrExcel MVP
... Strange is that once I go to the other wanted cell, the formula shows correct values =IF(N134>1,SUM(O134+365),0) but the result is the one like from the start.
What is even more stranger is that when I click on some of those incorrect cells, and do not change the formula and press enter then it gives me a proper result.
Make sure your Formulas --> Calculation Options are set to 'Automatic'.

#### Nalim

##### New Member
Make sure your Formulas --> Calculation Options are set to 'Automatic'.

That was it!!! Great, thanks, it works now perfectly.

#### Tetra201

##### MrExcel MVP
Glad to have been of help.

Replies
1
Views
372
Replies
5
Views
844
Replies
3
Views
633
Replies
4
Views
378
Replies
18
Views
3K

1,191,377
Messages
5,986,277
Members
440,016
Latest member
vasanrajeswaran

### 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?

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