Results 1 to 10 of 10

Thread: Workday
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Workday

    An extension to this problem:

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


    I want to amend this formula:

    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:

    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:

    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



    Last edited by tiredofit; Aug 23rd, 2019 at 05:39 AM.

  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workday

    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)

    ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workday

    Table1 Table2
    Field1 Field2 Date Field1 Field2 Date
    1 a 05/01/2010 1 a 04/01/2010
    2 b 06/01/2010 3 c 03/01/2010
    3 c 07/01/2010 4 d 04/01/2010
    4 d 08/01/2010 5 e 03/01/2010
    5 e 09/01/2010 2 b 06/01/2010

    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 by tiredofit; Aug 23rd, 2019 at 06:03 AM.

  4. #4
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workday

    Same question again.

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

    ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  5. #5
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workday

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

    This helped:

    Code:
    https://www.excelforum.com/excel-formulas-and-functions/922894-workday-function-and-array-formulas.html
    
    



    Last edited by tiredofit; Aug 23rd, 2019 at 06:11 AM.

  6. #6
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workday

    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 ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  7. #7
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workday

    Edit - deleted.
    Last edited by Gerald Higgins; Aug 23rd, 2019 at 06:32 AM.
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  8. #8
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workday

    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.

  9. #9
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workday

    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")
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  10. #10
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workday

    Almost.

    What I need is this:

    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 by tiredofit; Aug 23rd, 2019 at 06:52 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •