Please help me figure out how to MATCH completion date with Todays date

[mobman]

Hello I have the following scenario:

<TABLE style="WIDTH: 419pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=557 border=0><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" span=2 width=68><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" width=133><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 92pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=122 height=20></TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 51pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=68>Step 1</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 51pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=68>Step 2</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=82>Step 3</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 63pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=84>Todays Date:</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 100pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=133>Next Step is:</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Date Completed:</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>6/15/2011</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>6/17/2011</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>6/20/2011</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>6/18/2011 </TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Step 3</TD></TR></TBODY></TABLE>


Basically I am trying excel to state what the next step is based on the completion date and by comparing it with todays date. For example Step 3 is smaller than todays date so the next step would be step 3. Please help me figure this out, I have been trying really hard. Oh I will need this to be not in VBA. Perhaps a MATCH formula can help.

 

[Scott Huish]

Hi, welcome to the board!

Perhaps:

Excel Workbook
	A	B	C	D	E	F
1
2		Step 1	Step 2	Step 3	Todays Date:	Next Step is:
3	Date Completed:	6/15/2011	6/17/2011	6/20/2011	6/19/2011	Step 3
Sheet1

 

[mobman]

Hi, welcome to the board!

Perhaps:

Excel Workbook
	A	B	C	D	E	F
1
2		Step 1	Step 2	Step 3	Todays Date:	Next Step is:
3	Date Completed:	6/15/2011	6/17/2011	6/20/2011	6/19/2011	Step 3
Sheet1

Hello..

thank you for the reply. This formula only shows the last step no matter what the date is. Any other ideas??

 

[Aladin Akyurek]

Hello..

thank you for the reply. This formula only shows the last step no matter what the date is. Any other ideas??

Would you create a small set of examples along with the desired results?

 

[gecs]

Using the data layout posted above by HOTPEPPER, in F3 you may try:

=IF($E3<$B3,$B$2,IF($E3>$D3,"Finished",IF(ISNA(MATCH($E3,$B3:$D3,0)),INDEX($B$2:$D$2,MATCH($E3,$B3:$D3,1)+1),INDEX($B$2:$D$2,MATCH($E3,$B3:$D3,1)))))

 

[jasonb75]

Revising Hotpepper's suggestion

array formula, confirm with Shift Ctrl Enter.

=LOOKUP(MIN(IF(B3:D3>=E3,B3:D3)),B3:D3,B2:D2)

 

[mobman]

Would you create a small set of examples along with the desired results?

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 36px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD></TD><TD style="FONT-WEIGHT: bold">Step 1</TD><TD style="FONT-WEIGHT: bold">Step 2</TD><TD style="FONT-WEIGHT: bold">Step 3</TD><TD style="FONT-WEIGHT: bold">Todays Date:</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000">Next Step is:</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold">Date Completed:</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">6/15/2011</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">6/17/2011</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">6/20/2011</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">6/19/2011</TD><TD style="FONT-WEIGHT: bold">Step 3</TD></TR></TBODY></TABLE><TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold">Date Completed:</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">6/12/2011</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">6/22/2011</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">6/20/2011</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">6/19/2011</TD><TD style="FONT-WEIGHT: bold">Step 1</TD></TR></TBODY></TABLE><TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold">Date Completed:</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">6/16/2011</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">6/16/2011</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">6/25/2011</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">6/19/2011</TD><TD style="FONT-WEIGHT: bold">Step 3</TD></TR></TBODY></TABLE>
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold">Date Completed:</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">6/22/2011</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">6/26/2011</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">7/20/2011</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">6/19/2011</TD><TD style="FONT-WEIGHT: bold">Step 1</TD></TR></TBODY></TABLE>

Please view above-notice "Next Step Column". Basically i need it to show the step which has not yet exceeded todays date looking at all the dates and steps presented and by todays date i mean TODAY() not actually 6/19/2011 since that will change. The formula HOTPEPPA suggested only shows the latest step which is step 3 in this case not considering todays date.

 

[mobman]

Using the data layout posted above by HOTPEPPER, in F3 you may try:

=IF($E3<$B3,$B$2,IF($E3>$D3,"Finished",IF(ISNA(MATCH($E3,$B3:$D3,0)),INDEX($B$2:$D$2,MATCH($E3,$B3:$D3,1)+1),INDEX($B$2:$D$2,MATCH($E3,$B3:$D3,1)))))

Gecs, thank you for the efforts. But the formula needs to support x number of columns - the snipped I provided was an example only. This would work fine if I only had 3 columns however.

 

[gecs]

Gecs, thank you for the efforts. But the formula needs to support x number of columns - the snipped I provided was an example only. This would work fine if I only had 3 columns however.

It can be adapted for any number of columns:

=IF(TODAY()<OFFSET(steps,ROW()-ROW(steps),0,1,1),INDEX(steps,1),IF(TODAY()>OFFSET(steps,ROW()-ROW(steps),COLUMNS(steps)-1,1,1),"Finished",IF(ISNA(MATCH(TODAY(),OFFSET(steps,ROW()-ROW(steps),0,1,COLUMNS(steps)),0)),INDEX(steps,MATCH(TODAY(),OFFSET(steps,ROW()-ROW(steps),0,1,COLUMNS(steps)),1)+1),INDEX(steps,MATCH(TODAY(),OFFSET(steps,ROW()-ROW(steps),0,1,COLUMNS(steps)),1)))))

where steps is the name associated to the range where the steps names reside (in the above example $B$2:$D$2)

 

[jasonb75]

It can be adapted for any number of columns:

=IF(TODAY()<OFFSET(STEPS,ROW()-ROW(STEPS),0,1,1),INDEX(STEPS,1),IF(TODAY()>OFFSET(steps,ROW()-ROW(steps),COLUMNS(steps)-1,1,1),"Finished",IF(ISNA(MATCH(TODAY(),OFFSET(steps,ROW()-ROW(steps),0,1,COLUMNS(steps)),0)),INDEX(steps,MATCH(TODAY(),OFFSET(steps,ROW()-ROW(steps),0,1,COLUMNS(steps)),1)+1),INDEX(steps,MATCH(TODAY(),OFFSET(steps,ROW()-ROW(steps),0,1,COLUMNS(steps)),1)))))

where steps is the name associated to the range where the steps names reside (in the above example $B$2:$D$2)

Why use such a long and ugly formula (with errors) when a short and simple one will suffice?

If you look, you will see that I've amended hotpepper's suggestion in post #6. if you want to handle errors than all you need is something like

=IF(MAX(B3:D3)<E3,"Expired",LOOKUP(MIN(IF(B3:D3>=E3,B3:D3)),B3:D3,B2:D2))

Array confirmed with Shift Ctrl Enter