#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
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 |
#4
|
||||
|
||||
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 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 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 |