Solved: Date formula

[satyen]

Hi guys,

Wondering if anyone can help. Im struggling!

Came up with this formula. but need to look at a range i.e. compare 5 columns

[Formula]=IF(ISERROR(TODAY()-C3),"Error1", IF(AND(TODAY()-C3>0,TODAY()-C3>VLOOKUP(B3,H1:I6,2, FALSE)), "pas de data", IF(AND(TODAY()-C3<0,TODAY()-C3>-VLOOKUP(B3,H1:I6,2, FALSE)),C3,"pas de data")))[/Formula]

There are 5 columns.
I need to calculate if date in any of the columns falls within period then show date

Please see attached. I have put under past and future the results i expect to see.

Please let me know if not clear.

Many Thanks in advance

[satyen]

forgot to attach!

[Bob Phillips]

I think I am confused, but try this array formula

=IF(ISNUMBER(MATCH(TRUE,ABS(TODAY()-IF(C3:G3<>"NULL",C3:G3,100000))<VLOOKUP($B3,$L$1:$M$6,2,FALSE),0)),
INDEX(C3:G3,MATCH(TRUE,ABS(TODAY()-IF(C3:G3<>"NULL",C3:G3,100000))<VLOOKUP($B3,$L$1:$M$6,2,FALSE),0)),"No Dates")

[satyen]

thanks xld but this just brings up No dates every time evvn for correct dates entered. Id like to two formulas, one for past and one for future To work out if date in one of the five columns comes in the past and future period based on column B which is the key.

Example if u look at B7 that says T which accounts for 120 days. there is a date in D7- 12/12/10. For the past column it should show No dates as this date is in the future. For future it should show date as it is in the future and falls within 120 ahead period.


many thanks.

[Bob Phillips]

Maybe these

=IF(ISNUMBER(MATCH(TRUE,IF(($C3:$G3<>"NULL")*(--$C3:$G3<TODAY()),$C3:$G3,100000)-TODAY()<VLOOKUP($B3,$L$1:$M$6,2,FALSE),0)),
INDEX($C3:$G3,MATCH(TRUE,IF(($C3:$G3<>"NULL")*(--$C3:$G3<TODAY()),$C3:$G3,100000)-TODAY()<VLOOKUP($B3,$L$1:$M$6,2,FALSE),0)),"No Dates")

and

=IF(ISNUMBER(MATCH(TRUE,IF(($C3:$G3<>"NULL")*(--$C3:$G3>TODAY()),$C3:$G3,100000)-TODAY()<VLOOKUP($B3,$L$1:$M$6,2,FALSE),0)),
INDEX($C3:$G3,MATCH(TRUE,IF(($C3:$G3<>"NULL")*(--$C3:$G3>TODAY()),$C3:$G3,100000)-TODAY()<VLOOKUP($B3,$L$1:$M$6,2,FALSE),0)),"No Dates")

[satyen]

Keeps saying No dates, for everything I try. Would you be able to explain the formula, please. Thanks

[Bob Phillips]

You need to array enter the formulae.

[satyen]

sorry i dont understand

[Bob Phillips]

Edit the formula, then hit Ctrl-Shift-Enter, not just Enter.

[satyen]

Thanks,
that works but the calculation is not working properly. if for example the date 15/09/2007 and key is Q which 183 days 15/09/2007 is more than 183 days old so so cell should display No dates
I will have a long list of rows, will i have to CTRL+SHIFT+Enter for all cells for each row?

[Bob Phillips]

Try these two

=IF(ISNUMBER(MATCH(TRUE,IF(($C3:$G3<>"NULL")*(--$C3:$G3<TODAY()),TODAY()-$C3:$G3,100000)<VLOOKUP($B3,$L$1:$M$6,2,FALSE),0)),
INDEX($C3:$G3,MATCH(TRUE,IF(($C3:$G3<>"NULL")*(--$C3:$G3<TODAY()),TODAY()-$C3:$G3,100000)<VLOOKUP($B3,$L$1:$M$6,2,FALSE),0)),"No Dates")

and

=IF(ISNUMBER(MATCH(TRUE,IF(($C3:$G3<>"NULL")*(--$C3:$G3<TODAY()),TODAY()-$C3:$G3,100000)<VLOOKUP($B3,$L$1:$M$6,2,FALSE),0)),
INDEX($C3:$G3,MATCH(TRUE,IF(($C3:$G3<>"NULL")*(--$C3:$G3<TODAY()),TODAY()-$C3:$G3,100000)<VLOOKUP($B3,$L$1:$M$6,2,FALSE),0)),"No Dates")

You would drag-copy the formula, so you only need Ctrl-Shift-Enter for the first two.

[satyen]

thanks, but both these formulas are the same for past and future

[satyen]

the first one works!

[satyen]

the formula for past works but not future, i tried using > for future but doesnt work properly

[Bob Phillips]

I gave you the wrong for future, should have been

=IF(ISNUMBER(MATCH(TRUE,IF(($C3:$G3<>"NULL")*(--$C3:$G3>TODAY()),$C3:$G3-TODAY(),100000)<VLOOKUP($B3,$L$1:$M$6,2,FALSE),0)),
INDEX($C3:$G3,MATCH(TRUE,IF(($C3:$G3<>"NULL")*(--$C3:$G3>TODAY()),$C3:$G3-TODAY(),100000)<VLOOKUP($B3,$L$1:$M$6,2,FALSE),0)),"No Dates")

[satyen]

thank you for your efforts. but this still doesn't seem to work.. i even array entered formula, but when a future date for correct period is given it doesn't seem to change, just says 'No dates'..?

[Bob Phillips]

Well it worked for me on the test workbook that you provided.

If you provide a better test workbook I will look and see if I can spot any problems.

[satyen]

thanks, this works now. May be there is something wrong with my Excel at home.