PDA

View Full Version : Solved: Date formula



satyen
11-15-2010, 02:24 PM
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

=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")))

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
11-15-2010, 02:28 PM
forgot to attach!

Bob Phillips
11-15-2010, 04:20 PM
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
11-16-2010, 12:26 AM
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
11-16-2010, 01:57 AM
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
11-16-2010, 04:51 AM
Keeps saying No dates, for everything I try. Would you be able to explain the formula, please. Thanks

Bob Phillips
11-16-2010, 05:41 AM
You need to array enter the formulae.

satyen
11-16-2010, 07:33 AM
sorry i dont understand

Bob Phillips
11-16-2010, 08:03 AM
Edit the formula, then hit Ctrl-Shift-Enter, not just Enter.

satyen
11-16-2010, 08:37 AM
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
11-16-2010, 09:04 AM
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
11-16-2010, 09:58 AM
thanks, but both these formulas are the same for past and future

satyen
11-16-2010, 09:59 AM
the first one works!

satyen
11-16-2010, 10:25 AM
the formula for past works but not future, i tried using > for future but doesnt work properly

Bob Phillips
11-16-2010, 03:24 PM
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
11-17-2010, 12:51 AM
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
11-17-2010, 01:08 AM
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
11-17-2010, 05:32 AM
thanks, this works now. May be there is something wrong with my Excel at home.