 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / September 2007Using 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 One way: B1: =50000+500*INT((ROW()-1)/6) or, equivalently: B1: =500*INT((ROW()+599)/6) COpy down > 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 [quoted text clipped - 14 lines] > 12 1-Dec-08 50500 > 13 1-Jan-08 51000 Thanks very much for your help. > One way: > [quoted text clipped - 24 lines] > > 12 1-Dec-08 50500 > > 13 1-Jan-08 51000 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.  SignatureBiff Microsoft Excel MVP >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 [quoted text clipped - 16 lines] > 12 1-Dec-08 50500 > 13 1-Jan-08 51000 Thanks very much for your help. > Try this: > [quoted text clipped - 26 lines] > > 12 1-Dec-08 50500 > > 13 1-Jan-08 51000 You're welcome! SignatureBiff Microsoft Excel MVP > Thanks very much for your help. > [quoted text clipped - 32 lines] >> > 12 1-Dec-08 50500 >> > 13 1-Jan-08 51000 One way .. Put in B1: =SUM(50000,500*INT((ROWS($1:1)-1)/6)) Copy down SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > 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 [quoted text clipped - 14 lines] > 12 1-Dec-08 50500 > 13 1-Jan-08 51000 Thanks very much for your help. > One way .. > [quoted text clipped - 19 lines] > > 12 1-Dec-08 50500 > > 13 1-Jan-08 51000 welcome, Ashman SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > 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 > 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 [quoted text clipped - 14 lines] > 12 1-Dec-08 50500 > 13 1-Jan-08 51000 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. [quoted text clipped - 26 lines] > > 12 1-Dec-08 50500 > > 13 1-Jan-08 51000 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.