Workday

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,093
An extension to this problem:

Rich (BB code):
https://www.mrexcel.com/forum/excel-questions/1107696-approximate-match-vlookup.html



I want to amend this formula:

Rich (BB code):
=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:

Rich (BB code):
=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:

Rich (BB code):
=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>
 
Last edited:

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

Well-known Member
Joined
Apr 11, 2013
Messages
1,093
Table1Table2
Field1Field2DateField1Field2Date
1a05/01/20101a04/01/2010
2b06/01/20103c03/01/2010
3c07/01/20104d04/01/2010
4d08/01/20105e03/01/2010
5e09/01/20102b06/01/2010

<tbody>
</tbody>

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).
 
Last edited:

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Same question again.

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

?
 

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,093
I get #VALUE !, whether I enter it "normally" or as an array formula.

This helped:

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

[/FONT]
 
Last edited:

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Edit - deleted.
 
Last edited:

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,093
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

Well-known Member
Joined
Apr 11, 2013
Messages
1,093
Almost.

What I need is this:

Rich (BB code):
=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.



 
Last edited:

Forum statistics

Threads
1,082,318
Messages
5,364,528
Members
400,804
Latest member
davileal

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top