# Thread: Workday + Sat (2002)

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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.

7. ## Re: Workday + Sat (2002)

This is recurring issue. Check out, for example:

http://tinyurl.com/6j5kj

8. ## 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.

9. ## 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.)

10. ## 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. ## 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. ## 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. ## 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.

14. ## 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.

15. ## 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.

Page 1 of 2 12 Last

#### Posting Permissions

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