Workday + Sat (2002)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

This is recurring issue. Check out, for example:

http://tinyurl.com/6j5kj%5B/url%5D

Reply | Quote

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.

Reply | Quote

(Edited by JohnBF on 27-Jan-05 17:20. )

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

By the way, I see that this thread is your first series of Posts. Welcome to the Lounge! Have fun here.

Reply | Quote

As I am testing this, the script seems to over ride the holiday exclusion. This last Christmas and New Years were on Saturday, and they are included in the work week even though the Holiday range should cancel them out. Should I build in something that (see the attached file) basically says, if a date in H13:H32 is between G4:G6, add 1 day to G6? Obviously I’d have to move the formula in G6 to avoid a circular reference. My other alternative is to fake it out and add another (hidden) date into the Holiday range to compensate for Saturday holidays.

Reply | Quote

You may have to show me examples of what you mean. In my tests with your worksheet, loan dates of Dec 24, 2004, and Dec 31, 2004, correctly return, respectively, Dec 29, 2004, and Jan 5, 2005. See attachment. However, I do see two other possible problems:

1. My formula will not work with loan dates which are a Holiday or a Sunday; I assumed that a loan date could only be a workday; if that’s not the case, I’ll have to work on the function.

2. Your Holiday list contains two 2005 Holidays that fall on a Sunday; Independence Day and Christmas Day; the formula will not handle those correctly, because I used the lazy assumption that the observed day for most such holidays is the day after. Either your Holiday list should not include any Holiday that also falls on a Sunday, or I have to work on the function.

Reply | Quote

The law I am trying to conform to lists the dates that are the holidays. In this example, 12/25/2004 is the holiday. There are 4 of these dates. In my worksheet they are in blue. It doesn’t revert to the next Monday or the Friday before, regardless of whether you were off that day.

This is a law of rescission where you pledge your home on a loan, and you have 3 business days to back out if you want. The lender can’t fund until this period has passed. So a loan made on 12/24/04 with a 3 business day rescission period is like this: Sat. is a holiday, Sunday is always a non-business day, Mon-Wed are the business days so rescission ends at midnight Wed. 12/29 and the loan may be funded Thur. 12/30. If Saturday wasn’t a holiday, it would’ve counted.

The lender won’t close a loan on a Sunday or a holiday, but if the rescission period is over, they may fund it on that day.

In the case of a loan closed on 12/24/05, (not 04) Sunday doesn’t count, it is both Sunday and a holiday, and the 3 business days are Mon-Wed. So rescission ends at midnight 12/28/05 and the loan can fund 12/29/05.

So the calendar does work with a Sunday holiday, but not a Saturday holiday. It counts Saturday whether it is in the holiday range or not.

Does this explain it?

Reply | Quote

Part of the problem was an error in my logic (I hope that’s past tense), and based on your explanation I have changed the function so that legal Holidays which fall on Sunday are ignored. See if the attached works; you can see the days I tested it against.

It will return incorrect results if the Loan date is a Sunday or legal Holiday

Reply | Quote

This is fantastic and certainly more than I’d hoped for from this forum. Thank you very much.

Reply | Quote

Oops. In your attachment, the last column you added, “Vets 05” contains 2004 dates. The column is correct, but the display in the yellow box is not.
If a loan was closed on:
Wed 11-10-04 loan date
Thu 11-11 holiday,
Fri 11-12, counts day 1,
Sat 11-13 counts day 2
Sun 11-14 should not count
Mon 11-15 counts day 3,
so you can fund on Tuesday 11-16. The older version calculated this correctly. This version counts Sunday, which isn’t a business day.

It calculates it properly for 2005:
Thursday 11-10 is a loan date,
Fri 11-11 holiday
Sat 11-12 counts, day 1
Sun 11-13
Mon 11-14 counts, day 2
Tues 11-15 counts, day 3

Reply | Quote

Yes, you are right. There was still a flaw in my approach. I think the attached will work, please continue to test it.

Reply | Quote

I ran through weekday and weekend scenarios from each month as well as holidays from 11-04 through 12-05. Every entry was correct. I think you nailed it this time.

I can’t thank you enough. If you ever need an answer on a banking regulation, let me know. And hopefully a year or two from now I’ll understand the code you used and I’ll be able to return the favor with some other “newbie” like me.

Reply | Quote

(Edited by JohnBF on 27-Jan-05 16:22. )

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

Reply | Quote

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,Holiday)-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.

Reply | Quote

My formula doesn’t handle a number of situations, I didn’t test it well. I’ll work on a custom function.

Reply | Quote