Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count difference in days input box
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. -- By persisting in your path, though you forfeit the little, you gain the great. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count difference in days input box
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count difference in days input box
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("C2:D10").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 -- By persisting in your path, though you forfeit the little, you gain the great. "DavidH56" wrote: 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. -- By persisting in your path, though you forfeit the little, you gain the great. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count difference in days input box
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count difference in days input box
Thanks merjet,
Your post helped me to get exactly what I needed. have a great day thanks again -- By persisting in your path, though you forfeit the little, you gain the great. "merjet" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count distinct days from two groups of days | Excel Worksheet Functions | |||
Count Days more than 180 days prior to today | Excel Worksheet Functions | |||
When word says Install I want it count the days for 3 days w.o wee | Excel Worksheet Functions | |||
COUNT how many ROWS ago out of 10 days that the highest high in 10 days was made | Excel Worksheet Functions | |||
i have two days and i want the difference in days, months, year | Excel Worksheet Functions |