Highlight past & future events

R

rediproof

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
 
R

rediproof

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.
 
D

Domenic

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!
 
Top