If Formula and Dates

[perpetual159]

Hi, everyone

I'm not sure how to write an "If" function for dates and amounts and
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 exampl
that:

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

Based on this information, I have another table (Starting in column D
that has the 12 months (January, February, March, etc.) in Column D, an
Amount in column E. So, what I want each cell in column E, depending o
the month is the following rule: If, for example, today falls i
January, display today's amount; if today is not in January and Januar
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 an
formulas; it just shows what I want it to do (I also added a fe
comments, in case they help), so you all have an idea of what I want t
do.

Thanks

+-------------------------------------------------------------------
|Filename: sample sheet.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=812
+-------------------------------------------------------------------

 

[Spencer101]

Hi, everyone,

I'm not sure how to write an "If" function for dates and amounts and
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 exampl
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, an
Amount in column E. So, what I want each cell in column E, depending o
the month is the following rule: If, for example, today falls i
January, display today's amount; if today is not in January and Januar
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 an
formulas; it just shows what I want it to do (I also added a fe
comments, in case they help), so you all have an idea of what I want t
do.

Thanks!

I would change the months listed in column D, so they're actual date
rather than just months listed. So type "Jan13" (without the quotes
into D1 and copy down. You can custom format these cells to MMMM s
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

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Ron Rosenfeld]

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.

 

[perpetual159]

;1610477']

I think, from how you describe your process, that what you might want i
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 liste
for that month.

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

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

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

Thanks, Ron!

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

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Ron Rosenfeld]

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.