Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    New Lounger
    Join Date
    Jan 2005
    Location
    Texas, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Workday + Sat (2002)

    I want to add 3 work days to a start date. The work days exclude only Sundays and holidays. How do I add Saturday back in?

    Example, Friday 1/14/05 + 3 work days = 1/19/05 because we add Saturday, not Sunday, not Monday as it was a holiday in the US, add Tuesday and Wednesday.

  2. #2
    New Lounger
    Join Date
    Jan 2005
    Location
    Texas, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workday + Sat (2002)

    I generally try to read the formulas in English to understand what it says, but this one is tough. I made the formula substitutions and entered it as an array. I have the ToolPak on as I am familiar with both array formulas and the ToolPak.

    Here is my resulting formula useing defines names instead of cells where applicable:
    =WORKDAY(Loan_Date,3,Holiday)-SUM(IF(WEEKDAY(Loan_Date-1+ROW(INDIRECT("1:"&TRUNC(WORKDAY(Loan_Date,3,Holi day)-Loan_Date)+1)))=1,1,0))

    While some dates work, some are not. 1/12/05 + 3 = 1/15/05 but the formula is returning 1/17/05. Entering the 13th and 14th and it is fine, but the 15th should exclude Sun-Mon, the 16th & 17th, and equal the 20th. The formula here has the 19th.

    I appreciate the help. Is it worth trying to fix the formula? I haven't ever written a custom function so that is foreign.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Workday + Sat (2002)

    Date/Time: Doing WorkDay Math in VBA contains a series of functions to compute workdays in VBA. Although the code is from an Access handbook, it is not Access-specific. It shouldn't be too hard to modify the functions to consider Saturday a working day.

  4. #4
    New Lounger
    Join Date
    Jan 2005
    Location
    Texas, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workday + Sat (2002)

    I've never worked in VBA either. Is this cut and paste for what I need where I can read a bit on VBA and get this done, or am I better off trying to work it through in XL? (Years ago I was really into XL, even when it was called Multiplan on my old Mac. But I lost touch with the improvements as I wasn't doing much with spreadsheets for some years.)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Workday + Sat (2002)

    If you're new to VBA, it might be easier to stick with worksheet formulas. I'll see if I can come up with something later.

    Multiplan? That's really a long time ago! The first version of Excel (for the Macintosh) was released in 1985. Where does the time go? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: Workday + Sat (2002)

    My formula doesn't handle a number of situations, I didn't test it well. I'll work on a custom function.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workday + Sat (2002)

    This is recurring issue. Check out, for example:

    http://tinyurl.com/6j5kj
    Microsoft MVP - Excel

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: Workday + Sat (2002)

    Andy, please see the attached workbook which contains the custom function "sixdayworkweek" in the code module, and seems to be delivering correct results. (I tested against MLK's Birthday and against my 2004 company holidays of 11/25 and 11/26.) The "sixdayworkweek" function takes the same arguments as Excel's "workday" function but assumes that Monday through Saturday are workdays. Please test further.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: Workday + Sat (2002)

    <P ID="edit" class=small>(Edited by JohnBF on 27-Jan-05 16:22. )</P>Edit: Formula withdrawn, as it did not prove correct. The only smart thing I said in my original post text was:

    (I think this is a situation where it would be better to write a custom function.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    New Lounger
    Join Date
    Jan 2005
    Location
    Texas, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workday + Sat (2002)

    Interesting, and difficult to decipher. I am going to try and work with the formula as the function is beyond me at this time. I certainly welcome any other thoughts and help. I've spent all day on this and I know it has to be doable.

  11. #11
    New Lounger
    Join Date
    Jan 2005
    Location
    Texas, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workday + Sat (2002)

    John, I was just posting based on the other comments. I will look at this and I appreciate your time.

  12. #12
    New Lounger
    Join Date
    Jan 2005
    Location
    Texas, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workday + Sat (2002)

    I am sorry to say that I do not understand what you did, but it works great. Thank you very much. How long did it take you to write that and what suggested reading would you recommend so I could understand it? Or is this simply years of experience?

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: Workday + Sat (2002)

    If you are focusing on Excel VBA, then search this Forum and the VBA Forum for textbook recommendations. Julitta Korol's book on Excel 2000 VBA got me going, it's nicely organized, but John Walkenbach's Power programming VBA books are more extensive. And there are very heavy reference books for experienced coders.

    As for my skills, considering there are plenty of people much better than me, I think I can best explain them as being in direct proportion to the time I have spent beating my head against the wall.
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: Workday + Sat (2002)

    <P ID="edit" class=small>(Edited by JohnBF on 27-Jan-05 17:20. )</P>I think the functions and formulas in that link are trying to emulate the =networkdays() function (and the custom function seems unnecessarily complex), rather then the =workdays() function you need, try the custom function I wrote for your needs.

    The six workday equivalent to Excel's =networkdays() function should be:

    Function sixdaynetworkdays(rngStart As Range, rngEnd As Range, rngHolidays As Range)
    Dim rngCell As Range
    Dim lngStart As Long, lngEnd As Long, lngDiff As Long
    Dim lngC As Long

    Application.Volatile
    lngStart = CLng(rngStart.Value)
    lngEnd = CLng(rngEnd.Value)

    lngDiff = lngEnd - lngStart
    For lngC = 1 To lngDiff + 1
    ' if day is Sunday, subtract 1
    If Weekday(lngStart) = 1 Then lngDiff = lngDiff - 1
    For Each rngCell In rngHolidays
    ' if day is Holiday, subtract 1
    If lngStart = rngCell.Value Then lngDiff = lngDiff - 1
    Next rngCell
    lngStart = lngStart + 1
    Next lngC
    sixdaynetworkdays = CDate(lngDiff)
    End Function

    I should caveat that both the function in the worksheet and the one above are hardcoded to have Monday - Saturday workweeks.
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: Workday + Sat (2002)

    By the way, I see that this thread is your first series of Posts. Welcome to the Lounge! Have fun here.
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 2 12 LastLast

Posting Permissions

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