Fifteen Days From Today's Date, (Not Counting Saturday & Sunday).

StephenRay · #1 07-21-2017, 04:42 AM

I am just starting to learn VBA. I have a good book, and I search the 'net for examples to go by.

Ultimately, I want a Macro to add fifteen week-days to the current date and display the date in this format: 07-21-2017. -Not including Saturday and Sunday.
But I am stuck with a compile error. It says "Object Required" and it highlights:
Set iCounter = 0

All the examples I have seen make the counter an integer, and I have studied data objects. But can you make an object an integer?

Here's what I have written so far:

Sub Macro4()
'
' Macro4 Macro
'
'

Dim fifteenDaysFromNow As Date
Dim iCounter As Integer
'This next line is highlighted with the compile error
Set iCounter = 0
Dim day As DayOfWeek
Set DayOfWeek = currentDateTime.DayOfWeek

Do
fifteenDaysFromNow = DateAdd("d", 1, Date)
If day = DayOfWeek.Saturday Or day = DayOfWeek.Sunday Then
iCounter = -1
Else
iCounter = +1
Loop While iCounter <= 16
End If

End Sub

gmayor · #2 07-21-2017, 04:53 AM

I think you have made up some of your own VBA syntax there

The immediate problem you have mentioned is the use of Set
The line should be simply iCounter = 0, but then you will find other problems. Use the following function instead, which will add weekdays to a given date - here Today, so add 15 days to that and you get 11th August. The first macro shows how to call the function.

Code:

Sub Macro1()
Selection.TypeText Format(DateDue(Date, 15), "mm-dd-yyyy")
End Sub

Public Function DateDue(ByVal vDate As Date, ByVal iDays As Variant) As Variant
'Graham Mayor - http://www.gmayor.com - Last updated - 14 May 2017
'Add weekdays to date - from Monday if date falls at weekend
Dim iCount As Integer

    DateDue = Null
    If IsNull(vDate) Or IsNull(iDays) Then
        GoTo lbl_Exit
    End If

    Select Case Weekday(vDate)
        Case Is = 1    ' Sunday
            vDate = DateAdd("d", 1, vDate)
            iCount = 0
        Case Is = 7    ' Saturday
            vDate = DateAdd("d", 2, vDate)
            iCount = 0
        Case Else
            iCount = Weekday(vDate) - 2
    End Select

    iDays = iDays + iCount
    vDate = DateAdd("d", iCount * -1, vDate)
    DateDue = DateAdd("d", iDays Mod 5, DateAdd("ww", Int(iDays / 5), vDate))
lbl_Exit:
    Exit Function
End Function

StephenRay · #3 07-21-2017, 05:46 AM

gmayor, Thanks for your reply. Yes, indeed, I know my syntax skills are not skilled yet. The syntax may be from Visual Basic instead of VBA, (If there a difference?). I have good books for both Visual Basic and VBA. I get code from many different sources.
I will study your Macro and function.
And I need to study Functions too.
I will go work some more and get back to you if/when I get stuck.

StephenRay · #4 07-21-2017, 07:01 AM

Gmayor, Your code works!
Now I know how a function works. And you wrote it quick in just a few minutes! I like a man who knows his onions.
I am going to study this!
Later I will make it display using msg.
But don't tell me how. I will learn by struggling with it.

StephenRay · #5 07-21-2017, 11:03 AM

Gmayor,
I added to your code to highlight and copy the date.
It also displays a message in the Status Bar.
Here it is:
Sub Macro2()
'
'Sub Macro1()
Dim strBothItems As String
Dim firstPart As String
Dim datePart As String
firstPart = "Fifteen Days from Today "
datePart = Format(DateDue(Date, 15), "mm-dd-yyyy")
strBothItems = firstPart & datePart
Selection.TypeText Format(DateDue(Date, 15), "mm-dd-yyyy")
Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
Selection.Copy
Application.StatusBar = strBothItems
‘Application.StatusBar = Format(DateDue(Date, 15), "mm-dd-yyyy")
'Application.StatusBar = "Fifteen Days from Today "
End Sub

Public Function DateDue(ByVal vDate As Date, ByVal iDays As Variant) As Variant
'Graham Mayor - http://www.gmayor.com - Last updated - 14 May 2017
'Add weekdays to date - from Monday if date falls at weekend
Dim iCount As Integer

DateDue = Null
If IsNull(vDate) Or IsNull(iDays) Then
GoTo lbl_Exit
End If

Select Case Weekday(vDate)
Case Is = 1 ' Sunday
vDate = DateAdd("d", 1, vDate)
iCount = 0
Case Is = 7 ' Saturday
vDate = DateAdd("d", 2, vDate)
iCount = 0
Case Else
iCount = Weekday(vDate) - 2
End Select

iDays = iDays + iCount
vDate = DateAdd("d", iCount * -1, vDate)
DateDue = DateAdd("d", iDays Mod 5, DateAdd("ww", Int(iDays / 5), vDate))
lbl_Exit:
Exit Function
End Function

gmaxey · #6 07-22-2017, 12:47 PM

Couple of things.

1. As a new coder you have the same tendency as I had. You declare a lot of extraneous variables and you seem stuck on "Selection." Learn to use the range object and unless needed for clarity minimize variables.

Code:

Option Explicit
Sub Macro1()
Dim oRng As Range
  Set oRng = Selection.Range
  oRng.Text = Format(DateDue(Date, 15), "mm-dd-yyyy")
  'I assume  you want to copy this resulting date value to the clipboard for use later.
  'Otherwise this next line is not needed.
  oRng.Copy
  Application.StatusBar = "Fifteen Days from Today " & oRng.Text
End Sub

2. Discussed with Graham offline. His function actually returns the non-weekday after the span defined by iDays.

Code:

Public Function DateDue(ByVal vDate As Date, ByVal iDays As Variant) As Variant
'Graham Mayor - http://www.gmayor.com - Last updated - 14 May 2017
'Add weekdays to date - from Monday if date falls at weekend
Dim iCount As Integer
  DateDue = Null
  If IsNull(vDate) Or IsNull(iDays) Then
    GoTo lbl_Exit
  End If
  Select Case Weekday(vDate)
    Case Is = 1 ' Sunday
      vDate = DateAdd("d", 1, vDate)
      iCount = 0
   Case Is = 7 ' Saturday
      vDate = DateAdd("d", 2, vDate)
      iCount = 0
   Case Else
      iCount = Weekday(vDate) - 2
  End Select
  'Note, Graham's code actually returns the non-weekday 1 day after the span passed by iDays
  iDays = iDays + iCount
  'If you want the "iDay" use:
  'iDays = iDays + iCount - 1
  vDate = DateAdd("d", iCount * -1, vDate)
  DateDue = DateAdd("d", iDays Mod 5, DateAdd("ww", Int(iDays / 5), vDate))
lbl_Exit:
  Exit Function
End Function

What if your due date falls on Labor Day, Christmas, or some other holiday ;-)

gmayor · #7 07-22-2017, 08:24 PM

Quote:

Originally Posted by gmaxey

What if your due date falls on Labor Day, Christmas, or some other holiday ;-)

For that you'll need a look-up table - http://www.gmayor.com/FutureDateAddIn.htm

macropod · #8 07-23-2017, 03:06 PM

You can do all of this without a macro! To see how to do this and just about everything else you might want to do with dates in Word, check out my Microsoft Word Date Calculation Tutorial:
https://www.msofficeforums.com/word/...-tutorial.html
In particular, look at the item titled Handling Weekends and Holidays in Calculated Dates. Do read the document's introductory material.

StephenRay · #9 07-24-2017, 06:00 AM

Gentlemen, Thank you all for your interest. All of you are way ahead of me. This code certainly is elegant. I have been studying it all weekend.
I used to code elegant stuff like this in COBOL that nobody else could understand.
And right now, I am studying this line in the code:
DateDue = DateAdd("d", iDays Mod 5, DateAdd("ww", Int(iDays / 5), vDate))

This is very compact and elegant!
It will take me a few days to understand it because I have to study the basics first, and frankly, while this is really fun and really useful to me personally, I have many other responsibilities.

All of your words seem like gold to me. Thank you!

And yes, later I need to be able to add about ten different holidays, some of which change yearly. Christmas is always December 25, but our United States Thanksgiving Holiday is never on the same date. So I would need to be able to change the holiday dates.

macropod · #10 07-25-2017, 10:07 PM

FWIW, the field code solution I directed you to shows how to accommodate multiple holidays whose dates are calculated in a variety of ways, including:
• preset holidays, whose date is determined by their day number in a normal year;
• the Nth instance of a day of the week in a given month; and
• Easter,
and to substitute either the preceding or following weekday, as appropriate.

StephenRay · #11 07-26-2017, 04:55 AM

Macropod, Yes, I haven't had a chance to look at it yet, but I will. I want to get the holidays working with this. There are about ten of us here that administer Microsoft Active Directory and other employee documents. When I get it going I will share it with everybody. Thanks!

StephenRay · #12 07-26-2017, 05:08 AM

gmaxey, Thank you for your suggestion to study Range. I have lightly studied it but will study more tonight. Range is a method, not an object, my book says. I thought your oRng was a Key Word, and I couldn't find it anywhere! Ha! But now I know your Camel Notation shows it is a variable. And my book says to explicitly declare variables. So you see the trouble I have at this early stage.
From my experience with COBOL, I used to take hours studying some point or concept or piece of syntax that yielded big benefits later. Few people understood my code but it was compact and sophisticated and worked well.

Cosmo · #13 07-26-2017, 05:27 AM

BTW, not necessarily the solution you're looking for, but in this case all you have to do is add 21 days to the date (3 weeks), as this will always be 15 week days away (3 5-day weeks).

Doesn't work if you are looking for a date range that is not divisible by 5. Or if you need to accommodate holidays (which the original post doesn't mention as criteria)

gmaxey · #14 07-26-2017, 11:22 AM

SR,

Yes range is a method of Selection among other things but when declared as a variable, it is an object. Step through this code and watch your locals window.

Code:

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey, http://gregmaxey.com/word_tips.html, 7/26/2017
Dim oRng As Object
Dim oRng1 As Range
Dim oRng2
  Set oRng = Selection.Range
  Set oRng = Selection.Range
  Set oRng2 = Selection.Range
lbl_Exit:
  Exit Sub
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Counting the difference in days within multiple groups	wheddingsjr	Excel	2	03-28-2017 07:38 AM
Sum Function over Today +/- 60 Days	gabeha	Excel	2	09-12-2014 01:13 AM
Using Outlook Today Calendar Days	cwksr	Outlook	0	08-13-2014 10:57 AM
Conditional formatting of Today +21 days?	SHERMAN	Excel	3	12-20-2010 08:08 AM
Creating an Auto-Calc'd Date? Today+7 Days?	SoCalTelephone	Word	0	10-06-2010 10:27 AM