Simple Logic question?

[David Lipetz]

Using Excel 2003, I'm trying to build a formula that displays a symbol in a
cell to indicate the current date period.

There are four rows of data, each representing a calendar quarter Q1, Q2,
Q3, and Q4. Each row has a cell which indicates the last transaction date
for that period. In the Q1 row, that date is 03/31/08. In the Q2 row, that
date is 04/25/08 as that is the most current data set loaded. Q3 and Q4 date
cells are blank.

The first part of the formula needs to check if there is data by evaluating
if the date cell is blank. If it is blank, then the result should be a blank
(""). If not blank, proceed below.

The next part of the formula determines which quarter we are in based on
todays date.
In the Q1 row, if current month is equal to 1, 2, or 3, then display the
symbol.
In the Q2 row, if current month is equal to 4, 5, or 6, then display the
symbol.
In the Q3 row, if current month is equal to 7, 8, or 9, then display the
symbol.
In the Q4 row, if current month is equal to 10, 11, or 12, then display the
symbol.

The formula below (for Q1) does not work; it evaluates as volatile.
=IF($C43="","",IF(OR(MONTH(TODAY())=1,MONTH(TODAY())=2,MONTH(TODAY()=3)),"?",""))

$C43 is the last transaction date cell.

How should this formula be written?

Thanks,
David

 

[George Nicholson]

Each row has a cell which indicates the last transaction date for that

period.

In the Q1 row, if current month is equal to 1, 2, or 3, then display the
symbol.

(for Q1) could current month ever be anything but 1,2,3 or ""? (etc for Q2,
Q3, Q4) Can't you just check for ""?

 

[David Lipetz]

Current month can be 1-12. The four rows (Q1 - Q4) are always on the sheet.
Just checking for "" won't work since the date cell in Q1 will not be blank
but it is currently Q2.

 

[George Nicholson]

Your formula works fine for me, but here's an alternate approach. However,
whatever was preventing your formula from working for you may also be
present in mine.

(for Q1) =IF($C43="","",IF(CEILING(MONTH(TODAY()),3)=3,"?",""))
for Q2, 3 & 4 lines change =3 to =6, =9, =12 respectively

 

[David Lipetz]

Thanks George. Your formula worked.

The formula in my OP does not work for Q1. The date cell in that row is
03/31/08 and todays date is 04/28/08, yet the formula still returned the
symbol (an arrow - not a ?).

Both your formula and mine do show "volatile" for the result when evaluating
the formula using the Insert Function button with the cell selected. Not
sure what that means exactly.

Thanks again!

David

 

[Dave Mills]

Use VLookup
Create a table with dates and symbols
dateEndQ1,"Q1"
dateEndQ2,"Q2"
etc
=VLOOKUP(date you need,range col 1,range col 2)

Col 2 values will be returned up to the date in the same row.

You will need to play with the dates a bit. You may need the start date for the
period rather that the end date. For example 1/Apr in row 2 and 1/Jun in row 3
will mean Q2 will be returned from 1/Apr until the day before 1/Jun

I would use
1/Jan/1900,"StartCockUp"
1/Jan,"Q1"
1/Apr,"Q2"
1/Jul,"Q3"
1/Oct,"Q4"
1/Jan/NextYear,"EndCockUp"

then =VLOOKUP(date you need+1,Dates range,symbol range) should return one of the
six strings.