Count difference in days input box

[DavidH56]

Hi,

I am trying to make a macro with user input for counting the difference in
days. I would like to have the user input the start date and finish date.
Also I would like 3 answers to be given. Answer 1 Count days in 7 day week
which includes weekends (Sat and Sun). Answer 2 Count days in a 6 day week
with Saturdays only on weekends. Answer 3 Count days in a 5 day week which
excludes weekends. Also I would like to have the option of whether or not to
include my named range Holidays located in column "F". This is what I have to
start with and modify:

Sub DayDiff()
Dim sDate As String
Dim intNumDays As Integer
On Error GoTo errhandler
' Prompt for a date.
sDate = InputBox$("Enter a date in the mm/dd/yy format")
' Get difference between dates.
intNumDays = CInt(DateValue(sDate) - Now())
' Determine if difference is past or future.
If Sgn(intNumDays) = 1 Then
MsgBox "Days between " + sDate + " and today :" + CStr(intNumDays)
Else
MsgBox "The date " + sDate + " was" + CStr(Abs(intNumDays)) _
+ " day(s) ago."
End If
errhandler:
If Err > 0 Then MsgBox "Please enter a valid date."
End Sub

Any help that anyone could provide would be greatly appreciated.

 

[merjet]

A brute force method would loop day-by-day counting the number of
days, Saturdays and Sundays. The Weekday function, available in VBA,
can tell which days are Saturdays and Sundays.

Another route is to use the NetWorkDays worksheet formula.
Unfortunately this is not available in VBA, so you'd have to put the
start date, end date, and NetWorkDays formula on a worksheet somwhere
and then capture its result in VBA. Note: Networkdays counts both the
start day and end day!

Suppose iDays is the numbers days between two dates, counting both the
start and end days, i.e. 1+Int(EndDate-StartDate). iWkDays is the same
excluding Saturdays and Sundays. Then if iDays - iWkDays is even, the
number of days excluding Saturdays is 0.5*(iDays + iWkDays). If iDays
- iWkDays is odd:
(a) if the earliest day isn't a Sunday, the number of days excluding
Saturdays is 0.5*(iDays + iWkDays) - 0.5,
(b) if the earliest day is a Sunday, the number of days excluding
Saturdays is 0.5*(iDays + iWkDays) + 0.5.

A nice thing about using the NetWorkDays formula is it can exclude
holidays as well. See Excel's Help for the formula.

Hth,
Merjet

 

[DavidH56]

Thanks for your response Merjet,

I'm somewhat new in vba so I've tried the simpler approach. I've got the
networkdays function with cell b2 is start date ans c2 as finish date. this
is what i've found so far, however I can't seem to concantenate the cells to
display as combined c2 and d2 together down to c10 and d10.
Thanks again

Sub Using_InputBox_Method_For_Date_Diff()


Dim sDate As String
Dim fDate As String
Dim intNumDays As Integer

' Enter the Start Date.
sDate = InputBox$("Enter a Start Date in the mm/dd/yy format")
' Check to see if Cancel was pressed.
If sDate <> "" Then
' If not, write the number to the first cell in the first sheet.
Worksheets(1).Range("a2").Value = sDate
End If

' Enter the Finish Date.
fDate = InputBox$("Enter a Finish Date in the mm/dd/yy format")
' Check to see if Cancel was pressed.
If fDate <> "" Then
' If not, write the number to the first cell in the first sheet.
Worksheets(1).Range("b2").Value = fDate
End If


Range("C210").Select

' Display the selected cells, each value on separate row
MsgBox RangeToString(Selection, vbNewLine)

' Display a specified range, each value delimited by a space
MsgBox RangeToString(Sheet1.Range("c2:d10"), " ")


End Sub

Private Function RangeToString(ByRef rngDisplay As Range, ByVal strSeparator
As String) As String

'The string to separate elements on the message box,
'if the range size is more than one cell

Dim strMessage As String
Dim astrMessage() As String

Dim avarRange() As Variant
Dim varElement As Variant

Dim i As Long

'If the range is only one cell, we will return that that
If rngDisplay.Cells.Count = 1 Then

strMessage = rngDisplay.Value

'Else the range is multiple cells, so we need to concatenate their
values
Else

'Assign range to a variant array
avarRange = rngDisplay

' Loop through each element to build a one-dimensional array of the
range
For Each varElement In avarRange

ReDim Preserve astrMessage(i)
astrMessage(i) = CStr(varElement)
i = 1 + i

Next varElement

'Build the string to return
strMessage = Join(astrMessage, strSeparator)

End If

RangeToString = strMessage

End Function

 

[merjet]

Not knowing what you have outside A2:B2, I don't what your code is
trying to do after getting the Start Date and Finish Date. Maybe the
following will help. With the Start Date and Finish Date in A2 and B2,
enter the following formulas.
C2: =1+INT(B2-A2)
D2: =NETWORKDAYS(A2,B2)
E2: =IF(MOD(C2-
D2,2)=0,0.5*(C2+D2),IF(WEEKDAY(A2)=1,0.5*(C2+D2)+0.5,0.5*(C2+D2)-0.5))

These will give the counts of days, weekdays, and days excluding
Saturdays, respectively.

If you want the VBA code for them, turn on the macro recorder. Or
after entering the formulas, you can get the VBA code with the macro
recorder on by selecting each cell, putting your cursor at the end of
the formula, and hitting the Enter key.

Hth,
Merjet

 

[DavidH56]

Thanks merjet,

Your post helped me to get exactly what I needed.

have a great day

thanks again