| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using date as trigger for macro | Excel Discussion (Misc queries) | |||
| Date-related problems - max and datevalue | Excel Discussion (Misc queries) | |||
| How can I get a date in exel to trigger a reminder in 30 days? | Excel Discussion (Misc queries) | |||
| Recurring annual events using a specific date as a trigger date | Excel Worksheet Functions | |||
| DATEVALUE OF CURRENT DATE | Excel Worksheet Functions | |||
Linear Mode
