Workday

[tiredofit]

An extension to this problem:

https://www.mrexcel.com/forum/excel-questions/1107696-approximate-match-vlookup.html

I want to amend this formula:

=IF(C3>INDEX($I$3:$I$7,MATCH(A3&"|"&B3,INDEX($G$3:$G$7&"|"&$H$3:$H$7,0),0)),"Y","N")

to this:

=IF(C3>INDEX($I$3:$I$7,MATCH(A3&"|"&B3,INDEX($G$3:$G$7&"|"&$H$3:$H$7,0),0))+3,"Y","N")

and it's fine.

But actually I want to use the WORKDAY function:

=IF(C3>INDEX($I$3:$I$7,MATCH(A3&"|"&B3,INDEX($G$3:$G$7&"|"&$H$3:$H$7,0),0))+WORKDAY($I$3:$I$7,3),"Y","N")

but it's not returning the correct results.

Can someone tell me what's wrong?

Thanks


_{^{<strike>
</strike>}}

 

[Gerald Higgins]

What results are you getting ?
What do you think the results should be, and why ?

According to Excel's built in help function, the WORKDAY function requires its first argument to be a reference to a single date, the start date.
You seem to be giving it a range.
What results do you get for this on its own . . .
WORKDAY($I$3:$I$7,3)

?

 

[tiredofit]

[TABLE="class: cms_table, width: 682"]
<tbody>[TR]
[TD="colspan: 3, align: center"]Table1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3, align: center"]Table2[/TD]
[/TR]
[TR]
[TD]Field1[/TD]
[TD]Field2[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Field1[/TD]
[TD]Field2[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]a[/TD]
[TD="align: right"]05/01/2010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]a[/TD]
[TD="align: right"]04/01/2010[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]b[/TD]
[TD="align: right"]06/01/2010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]c[/TD]
[TD="align: right"]03/01/2010[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]c[/TD]
[TD="align: right"]07/01/2010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]d[/TD]
[TD="align: right"]04/01/2010[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]d[/TD]
[TD="align: right"]08/01/2010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]e[/TD]
[TD="align: right"]03/01/2010[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]e[/TD]
[TD="align: right"]09/01/2010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]b[/TD]
[TD="align: right"]06/01/2010[/TD]
[/TR]
</tbody>[/TABLE]

What I am trying to do is if Field1 matches AND Field2 matches AND the date in Table1 is later than the date in Table2 AND if the date in Table1 is earlier than the (date in Table2 +3 workdays).

 

[Gerald Higgins]

Same question again.

What results do you get for this on its own . . .
WORKDAY($I$3:$I$7,3)

?

 

[tiredofit]

I get #VALUE !, whether I enter it "normally" or as an array formula.

This helped:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://www.excelforum.com/excel-formulas-and-functions/922894-workday-function-and-array-formulas.html

[/FONT]

 

[Gerald Higgins]

Yes, I think you're using the workday function incorrectly.

It looks like you're trying to add 3 working days to some start date, yes ?

I think you need to specify what the start date actually is, and use that in the workday function.

For example
+workday(a1,3)
where the start date is specified in A1.

I know that you are not actually specifying the start date in A1.
So, question, how are you actually identifying the start date ?

 

[Gerald Higgins]

Edit - deleted.

 

[tiredofit]

Afraid it doesn't return the correct result.

I'm going to add additiional columns and build it, instead of having one single (but massive) formula.

 

[Gerald Higgins]

How about this ?

=IF(C3>WORKDAY(INDEX($I$3:$I$7,MATCH(A3&"|"&B3,INDEX($G$3:$G$7&"|"&$H$3:$H$7,0),0)),3),"Y","N")

 

[tiredofit]

Almost.

What I need is this:

=IF(C3<=WORKDAY(INDEX($I$3:$I$7,MATCH(A3&"|"&B3,INDEX($G$3:$G$7&"|"&$H$3:$H$7,0),0)),3),"Y","N")

​

Thanks for your help.



​