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

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

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

One way:

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

or, equivalently:

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


COpy down

In article ,
Ashman wrote:

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

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

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.

--
Biff
Microsoft Excel MVP


"Ashman" wrote in message
...
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

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

One way ..

Put in B1:
=SUM(50000,500*INT((ROWS($1:1)-1)/6))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ashman" wrote:
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

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

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" wrote:

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

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

Thanks very much for your help.

"JE McGimpsey" wrote:

One way:

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

or, equivalently:

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


COpy down

In article ,
Ashman wrote:

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

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

Thanks very much for your help.

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Ashman" wrote in message
...
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

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

Thanks very much for your help.

"Max" wrote:

One way ..

Put in B1:
=SUM(50000,500*INT((ROWS($1:1)-1)/6))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ashman" wrote:
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

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

Thanks very much for your help.

"OssieMac" wrote:

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" wrote:

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

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

welcome, Ashman
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ashman" wrote in message
...
Thanks very much for your help.

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

You're welcome!

--
Biff
Microsoft Excel MVP


"Ashman" wrote in message
...
Thanks very much for your help.

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Ashman" wrote in message
...
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