Using a date (datevalue) to trigger adding a value to a cell

[Ashman]

I have a list of the months over the next 3 years in column A (format is
01-Jan-08). Next to that list there is another list of values (starting at
$50,000) next to each date. In January and July each year (except for Jan
08), I want $500 added to the $50,000, so by the end of the 2nd year, the
value is $52,000. Do I use DATEVALUE in an IF or something else? Below is
an example of what I mean. Please help!

A B
1 1-Jan-08 50000
2 1-Feb-08 50000
..
..
6 1-Jun-08 50000
7 1-Jul-08 50500
..
..
12 1-Dec-08 50500
13 1-Jan-08 51000

 

[JE McGimpsey]

One way:

B1: =50000+500*INT((ROW()-1)/6)

or, equivalently:

B1: =500*INT((ROW()+599)/6)


COpy down

 

[T. Valko]

Try this:

Enter 50,000 in B1

Enter this formula in B2 and copy down as needed:

=B1+OR(MONTH(A2)={1,7})*500

Assumes there are no empty cells within your date range.

 

[Max]

One way ..

Put in B1:
=SUM(50000,500*INT((ROWS($1:1)-1)/6))
Copy down

 

[OssieMac]

Enter $50,000 against the first date and then enter this formula in the cell
under it and then copy the formula to the bottom.

Assumes you have column headers and therefore first date in cell A2 and
$50,000 in cell B2. Enter formula in B3 and copy down.

=IF(OR(MONTH(A3)=1,MONTH(A3)=7),B2+500,B2)

Regards,

OssieMac

 

[Ashman]

Thanks very much for your help.

Try this:

Enter 50,000 in B1

Enter this formula in B2 and copy down as needed:

=B1+OR(MONTH(A2)={1,7})*500

Assumes there are no empty cells within your date range.

 

[Ashman]

Thanks very much for your help.

One way:

B1: =50000+500*INT((ROW()-1)/6)

or, equivalently:

B1: =500*INT((ROW()+599)/6)


COpy down

 

[Ashman]

Thanks very much for your help.

One way ..

Put in B1:
=SUM(50000,500*INT((ROWS($1:1)-1)/6))
Copy down

 

[Ashman]

Thanks very much for your help.

Enter $50,000 against the first date and then enter this formula in the cell
under it and then copy the formula to the bottom.

Assumes you have column headers and therefore first date in cell A2 and
$50,000 in cell B2. Enter formula in B3 and copy down.

=IF(OR(MONTH(A3)=1,MONTH(A3)=7),B2+500,B2)

Regards,

OssieMac

 

[Max]

welcome, Ashman

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks very much for your help.