Excel If Statements and Conditional Formating

[Superbaker]

Let me see if I can explain this:

970000
4/27/1999 985000
5/16/1999

Image this is a piece of a spreadsheet. The dates will keep going down in the left column and the number will slowly be going up. Is there away to use an if statement or conditional formatting to look at the cell with 4/27/1999 and if it is after the 12th of that month (which this is) to add 800 to the 970000. The ultimate goal would be to be able to apply this to the whole sheet so the next line, 5/16/1999 will also be checked and since its after the 12th of that month, you would add 800 to the 985000 number. I know i explained it really bad so try and follow what I am trying to accomplish. She wants to apply this to a sheet that has dates as far back as 1999 up to the present. She like to have this apply all the way across so everynumber will be updated if the date is after the 12th.

 

[chrisaroundtown]

Try this;

I don't know where your valus come from so lets say each value is 15000 higher than the last and if it's past the 12th of the month you want to add 800. I use this as an example,

27/4/1999 970000
28/4/1999 =B2+15000
29/4/1999 =B3+15000

Change to this
27/4/1999 =if(DAY(A1)>11,(97000)+800,97000)
28/4/1999 =if(DAY(A2)>11,(B2+15000)+800,B2+15000)
29/4/1999 =if(DAY(A3)>11,(B3+15000)+800,B3+15000)


This is basically saying that if the day of 27/4/1999 is greater than 11 then add 800 if not then leave as 97000.

Let me know if your situation and I will help you out further.

Thanks
Chris

 

[KenWright]

Assuming dates in A3:A100, Numbers in B3:B100, in cell C3 put the following formula:-

=IF(DAY(A3)>12,B3+800,B3)

Now simply copy down. Once done, you can either keep boths ets of data (ie Cols B&C), or if you only want to keep the amended data, then simply select all the data in Col C, do Edit / Copy, then Edit / Paste Special / Values Only.

Now delete Column B and Col C will fall back to become your new Col B, all adjusted as required.

Regards
Ken................