Highlight past & future events
 

I am working on a reporting spreadsheet and need some help. The report
needs to show any milestone within +/- 15 days of today (date the
report is being run)

The formula needs to work something like this:

From x range of cells in a row find all that are +/- from today and
then take the info from the cell left of this.


Any help would be appreciated


--
rediproof
------------------------------------------------------------------------
rediproof's Profile: http://www.excelforum.com/member.php...o&userid=23714
View this thread: http://www.excelforum.com/showthread...hreadid=373896

Can you provide a sample of your data along with your expected results?


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=373896

The data is kind of set out like this but with multiple rows of
information for different projects:

MDESC1 - MILE1 - MDESC2 - MILE2
Kick Off - 19/05/05 - PID - 21/05/05

For the result I want something like:

*Milestones this month* (May)
Kick Off, PID (in one cell)

The report has to be run part halfway through the month but I need the
information for the 2 weeks prioir and after the report date.


--
rediproof
------------------------------------------------------------------------
rediproof's Profile: http://www.excelforum.com/member.php...o&userid=23714
View this thread: http://www.excelforum.com/showthread...hreadid=373896

Assumptions:

1) A1:D2 contains your data

2) A10 contains your current date

Formulas:

B10:

=SUM(IF(ISNUMBER(A2:D2),(ABS(A2:D2-A10)<=15)+0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

C10, copied across:

=IF(COLUMNS($C10:C10)<=$B$10,INDEX($A$2:$D$2,SMALL (IF((ISNUMBER($A$2:$D$2)),IF(ABS($A$2:$D$2-$A$10)<=15,COLUMN($A$2:$D$2)-COLUMN($A$2))),COLUMNS($C10:C10))),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=373896

Thanks again


--
rediproof
------------------------------------------------------------------------
rediproof's Profile: http://www.excelforum.com/member.php...o&userid=23714
View this thread: http://www.excelforum.com/showthread...hreadid=373896