IF - Date falls between two dates, place here
 

Good morning,
My brain is in drain mode today, so I am asking for your expert help.
Spreadsheet : Column F contains dates in each row - dates vary from any date
in 2009 to any date in 2011.
Column U ~ BO Header row contains dates to the first of each month ( U =
09/11, W = 09/12, X = 1/10 ) formated to show m/y.
I have played with the IF - statement, but I'm not getting what I need.
I would like to have the cell under each m/y to place that date in, IF it
falls within that month/year... or IF it falls between the m/y of the column
to the left and the column to the right - then place date here....

=IF($F13<AC5,$F13,IF($F13Y5,$F13,""))

but this places the date in every cell that contains this formula, but I
would like it to be empty, when it no longer falls within the dates of the
header row to its right and left.

Thanks you if you can enlighten me of my "between" mistake!
Driftwood

IF - Date falls between two dates, place here
 

OK, I found out what I was doing wrong - the formula below works, thanks to
re-wording my search for an answer. But the "--" have me stumped, I will need
to learn more about this.
For now I wil prove out different scenarios with the formula to ensure I
have correctly addressed the cells of reference.

=IF(SUMPRODUCT(--(U$5<=$F13),--(Y$5=$F13)),$F13,"")


"Driftwood" wrote:

Good morning,
My brain is in drain mode today, so I am asking for your expert help.
Spreadsheet : Column F contains dates in each row - dates vary from any date
in 2009 to any date in 2011.
Column U ~ BO Header row contains dates to the first of each month ( U =
09/11, W = 09/12, X = 1/10 ) formated to show m/y.
I have played with the IF - statement, but I'm not getting what I need.
I would like to have the cell under each m/y to place that date in, IF it
falls within that month/year... or IF it falls between the m/y of the column
to the left and the column to the right - then place date here....

=IF($F13<AC5,$F13,IF($F13Y5,$F13,""))

but this places the date in every cell that contains this formula, but I
would like it to be empty, when it no longer falls within the dates of the
header row to its right and left.

Thanks you if you can enlighten me of my "between" mistake!
Driftwood

IF - Date falls between two dates, place here
 

The "--" is to change the text output of your boolean check into 1's and
0's. The first "-" changes TRUE/FALSE to -1/0, and the second "-" changes it
to 1/0.

But actually, SUMPRODUCT is more used when trying to check a criteria
against multiple rows. I think your formual need simple be:
=IF(AND(U$5<=$F13,Y$5=$F13),$F13,"")

--
Best Regards,

Luke M
"Driftwood" wrote in message
...
OK, I found out what I was doing wrong - the formula below works, thanks
to
re-wording my search for an answer. But the "--" have me stumped, I will
need
to learn more about this.
For now I wil prove out different scenarios with the formula to ensure I
have correctly addressed the cells of reference.

=IF(SUMPRODUCT(--(U$5<=$F13),--(Y$5=$F13)),$F13,"")


"Driftwood" wrote:

Good morning,
My brain is in drain mode today, so I am asking for your expert help.
Spreadsheet : Column F contains dates in each row - dates vary from any
date
in 2009 to any date in 2011.
Column U ~ BO Header row contains dates to the first of each month ( U
=
09/11, W = 09/12, X = 1/10 ) formated to show m/y.
I have played with the IF - statement, but I'm not getting what I need.
I would like to have the cell under each m/y to place that date in, IF
it
falls within that month/year... or IF it falls between the m/y of the
column
to the left and the column to the right - then place date here....

=IF($F13<AC5,$F13,IF($F13Y5,$F13,""))

but this places the date in every cell that contains this formula, but I
would like it to be empty, when it no longer falls within the dates of
the
header row to its right and left.

Thanks you if you can enlighten me of my "between" mistake!
Driftwood

IF - Date falls between two dates, place here
 

Hi

The tests in the Sumproduct formula, will return either True or False.
Sumproduct needs numbers to deal with.
The double unary minus -- coerces a True value to 1 and a False value to 0
The same effect is achieved if you multiply the value by 1, or add 0 the
value.
The formula could be written as
=IF(SUMPRODUCT((U$5<=$F13)*(Y$5=$F13)),$F13,"")
where the act of multiplying forces the coercion.
--
Regards
Roger Govier

Driftwood wrote:
OK, I found out what I was doing wrong - the formula below works, thanks to
re-wording my search for an answer. But the "--" have me stumped, I will need
to learn more about this.
For now I wil prove out different scenarios with the formula to ensure I
have correctly addressed the cells of reference.

=IF(SUMPRODUCT(--(U$5<=$F13),--(Y$5=$F13)),$F13,"")


"Driftwood" wrote:

Good morning,
My brain is in drain mode today, so I am asking for your expert help.
Spreadsheet : Column F contains dates in each row - dates vary from any date
in 2009 to any date in 2011.
Column U ~ BO Header row contains dates to the first of each month ( U =
09/11, W = 09/12, X = 1/10 ) formated to show m/y.
I have played with the IF - statement, but I'm not getting what I need.
I would like to have the cell under each m/y to place that date in, IF it
falls within that month/year... or IF it falls between the m/y of the column
to the left and the column to the right - then place date here....

=IF($F13<AC5,$F13,IF($F13Y5,$F13,""))

but this places the date in every cell that contains this formula, but I
would like it to be empty, when it no longer falls within the dates of the
header row to its right and left.

Thanks you if you can enlighten me of my "between" mistake!
Driftwood