Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-20-2021, 07:40 AM
whatstheword whatstheword is offline Calculating number of work dates between two days Windows 10 Calculating number of work dates between two days Office 2016
Novice
Calculating number of work dates between two days
 
Join Date: Oct 2021
Posts: 2
whatstheword is on a distinguished road
Default Calculating number of work dates between two days

Hello there,


I'm trying to create a macro for a Microsoft Word document that returns the number of the business days (i.e. no Sunday and Saturday) between an inputted start date and the current date.


I tried to search for/write code that excludes weekends and insert it into the following macro, but have been unsuccessful thus far.


Your help would be much appreciated!



Sub CalculateDateDifference()
Dim xStartDate As Date
Dim xEndDate As Date


Dim xDay As Long
On Error Resume Next
xStartDate = InputBox("Enter the start date", "Start Date", "")
xEndDate = Date
If (InStr(1, Str(xStartDate), ":") > 0) Or (InStr(1, Str(xEndDate), ":") > 0) Then
MsgBox "please input current date", vbInformation, "Date Calculation"
Exit Sub
End If
xDay = DateDiff("d", xStartDate, xEndDate)
MsgBox "There are " & xDay & " days from " & xStartDate & " to " & xEndDate & vbCrLf, vbInformation, "Date Calculation"
End Sub
Reply With Quote
  #2  
Old 10-20-2021, 09:15 PM
gmayor's Avatar
gmayor gmayor is offline Calculating number of work dates between two days Windows 10 Calculating number of work dates between two days Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

One way would be to use the Weekday function to establish if each day in the range is a weekday and count those that are. e.g.
Code:
Sub CalculateDateDifference()
Dim xStartDate As Date
Dim sDate As String
Dim xEndDate As Date, dTestDate As Date
Dim xDay As Long, lCount As Long, i As Long
    On Error Resume Next
    xEndDate = Date
    sDate = InputBox("Enter the start date", "Start Date", "")
    If IsDate(sDate) = False Then
        MsgBox "Please input a valid date.", vbInformation, "Date Calculation"
        Exit Sub
    End If
    xStartDate = CDate(sDate)
    If xStartDate > xEndDate Then
        MsgBox "The start date must be before the end date!", vbInformation, "Date Calculation"
        Exit Sub
    End If
    xDay = DateDiff("d", xStartDate, xEndDate)
    lCount = 0: dTestDate = xStartDate
    For i = 1 To xDay
        If Weekday(dTestDate) > 1 And Weekday(dTestDate) < 7 Then lCount = lCount + 1
        dTestDate = dTestDate + 1
    Next i
    MsgBox "There are " & lCount & " work days from " & xStartDate & " to " & xEndDate & vbCrLf, vbInformation, "Date Calculation"
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 10-21-2021, 08:55 AM
whatstheword whatstheword is offline Calculating number of work dates between two days Windows 10 Calculating number of work dates between two days Office 2016
Novice
Calculating number of work dates between two days
 
Join Date: Oct 2021
Posts: 2
whatstheword is on a distinguished road
Default

That worked beautifully. Thank you!


Would you be able to do another version that strips out weekends AND holidays? Where I can input the holidays into an array like what you did in the suggested code here: https://www.msofficeforums.com/word-...-holidays.html
Reply With Quote
  #4  
Old 10-21-2021, 09:10 PM
gmayor's Avatar
gmayor gmayor is offline Calculating number of work dates between two days Windows 10 Calculating number of work dates between two days Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

I had forgotten that post. However I think the following should work
Code:
Sub CalculateDateDifference()
'Graham Mayor - https://www.gmayor.com - Last updated - 22 Oct 2021
Dim xStartDate As Date
Dim sDate As String
Dim xEndDate As Date, dTestDate As Date
Dim xDay As Long, lCount As Long, i As Long, j As Long
Dim vHolidays As Variant

    vHolidays = Array("01/10/21", "02/10/21", "30/10/21")
    On Error Resume Next
    xEndDate = Date
    sDate = InputBox("Enter the start date", "Start Date", "")
    If IsDate(sDate) = False Then
        MsgBox "Please input a valid date.", vbInformation, "Date Calculation"
        Exit Sub
    End If
    xStartDate = CDate(sDate)
    If xStartDate > xEndDate Then
        MsgBox "The start date must be before the end date!", vbInformation, "Date Calculation"
        Exit Sub
    End If
    xDay = DateDiff("d", xStartDate, xEndDate)
    lCount = 0: dTestDate = xStartDate
    For i = 1 To xDay
        If Weekday(dTestDate) > 1 And Weekday(dTestDate) < 7 Then lCount = lCount + 1
        For j = 0 To UBound(vHolidays)
            If dTestDate = CDate(vHolidays(j)) Then
                lCount = lCount - 1
            End If
        Next j
        dTestDate = dTestDate + 1
    Next i
    MsgBox "There are " & lCount & " work days from " & xStartDate & " to " & xEndDate & vbCrLf, vbInformation, "Date Calculation"
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating number of work dates between two days Calculating work in progress between two dates PowerStar Excel 7 06-07-2021 04:03 AM
Formula for calculating dates based on Business Days jthomas666 Excel 2 03-02-2020 09:05 AM
Default How to automatically Calculate number of days between two dates? wcngu1 Word 6 10-24-2019 04:20 AM
Calculating number of work dates between two days Calculating Quarterly Dates by Months not Days Artboy34 Excel 3 01-28-2016 09:47 AM
Calculating 6.5hr work days altja Project 3 02-10-2014 03:11 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:36 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft