If Formula and Dates
 

Hi, everyone,

I'm not sure how to write an "If" function for dates and amounts and I was hoping to get some help. So here's my scenario:

I have a spreadsheet in which I keep track of amounts logged every day. These amounts may increase or decrease gradually, so imagine for example that:

Column A: Entire Year from 1/1/2013 to 12/31/2013
Column B: Fluctuating Amounts

Based on this information, I have another table (Starting in column D) that has the 12 months (January, February, March, etc.) in Column D, and Amount in column E. So, what I want each cell in column E, depending on the month is the following rule: If, for example, today falls in January, display today's amount; if today is not in January and January has already passed, show amount logged for January 31 (in other words, the last day of that month); if January hasn't happened, show $0.00.

I hope this makes sense, and I appreciate any help I can get in this! I've attached a sample of the spreadsheet, but it does not have any formulas; it just shows what I want it to do (I also added a few comments, in case they help), so you all have an idea of what I want to do.

Thanks!

I would change the months listed in column D, so they're actual dates rather than just months listed. So type "Jan13" (without the quotes) into D1 and copy down. You can custom format these cells to MMMM so they look the same as yours currently do.

Then I would put the formula below in E1 and copy down.

=IF(MONTH(TODAY())=MONTH(D1),VLOOKUP(TODAY(),$A$1: $B$365,2,FALSE),VLOOKUP(EOMONTH(D1,0),$A$1:$B$365, 2,FALSE))

Hopefully that does what you need.

S.

If Formula and Dates
 

On Tue, 19 Mar 2013 23:52:02 +0000, perpetual159 wrote:

Hi, everyone,

I'm not sure how to write an "If" function for dates and amounts and I
was hoping to get some help. So here's my scenario:

I have a spreadsheet in which I keep track of amounts logged every day.
These amounts may increase or decrease gradually, so imagine for example
that:

Column A: Entire Year from 1/1/2013 to 12/31/2013
Column B: Fluctuating Amounts

Based on this information, I have another table (Starting in column D)
that has the 12 months (January, February, March, etc.) in Column D, and
Amount in column E. So, what I want each cell in column E, depending on
the month is the following rule: If, for example, today falls in
January, display today's amount; if today is not in January and January
has already passed, show amount logged for January 31 (in other words,
the last day of that month); if January hasn't happened, show $0.00.

I hope this makes sense, and I appreciate any help I can get in this!
I've attached a sample of the spreadsheet, but it does not have any
formulas; it just shows what I want it to do (I also added a few
comments, in case they help), so you all have an idea of what I want to
do.

Thanks!

I think, from how you describe your process, that what you might want is the amount from column B that corresponds to the last "filled in date" of the month listed in column D; and a blank if there is nothing listed for that month.

E1: =IFERROR(LOOKUP(2,1/((MONTH($A$1:$A$366)=MONTH("1 "&D1))*($B$1:$B$366<"")),$B$1:$B$366),"")

will return the last amount logged in the month in D1. Fill down to E12.

If the month is blank, the formula returns a null string (""). If you prefer it to return a zero (0) which is what you have in your sample worksheet, change the "" to a 0.

Thanks, Ron!

I'm sorry I did not answer sooner, but I finally got around to to try your formula, and it works perfectly! Thanks again!

If Formula and Dates
 

On Fri, 26 Apr 2013 12:42:55 +0100, perpetual159 wrote:

Thanks, Ron!

I'm sorry I did not answer sooner, but I finally got around to to try
your formula, and it works perfectly! Thanks again!

Glad to help. Thanks for the feedback.