0

I have a number of records in a sheet, each with a start and end date. Each line has a record number, with some lines having record numbers the same as other lines (The sheet lists cases and dates that case was used. Some cases have 1 line only, while others will have several lines)

The dates for each case will not overlap, however there may be gaps between each range

For example:

Case# -- Start Date -- End Date
Case 1 - 01/01/2018 - 05/01/2018
Case 1 - 06/01/2018 - 13/01/2018
Case 1 - 17/03/2018 - 20/03/2018
Case 2 - 14/02/2018 - 15/02/2018
Case 3 - 19/05/2018 - 25/05/2018
Case 3 - 26/05/2018 - 28/05/2018

I need to know how many weeks these date ranges cover. So for Case 1, it would be 4 weeks (the start date of the second line is still within the same week as the first line, so no need to count the week number twice, the third line actually occurs over 2 different weeks, so would be counted as 2 weeks)

I can get the week number for the dates (Using ISOWEEKNUM) but I need a formula that would look at all lines for that case#, and to not include the weeks in the gaps. If I use the earliest date and then the lastest date for Case 1, the number of weeks would be 12...

If anyone can assist I would be most appreciative, I haven't been able to find anything in my searches

TIA Cheers, Nat

Natalie Hunt
  • 23
  • 5

1 Answers1

0

Write a user defined function and use it in the sheet? The UDF below takes four arguments:

arg1 Range caseNumber: This is the range containing the case number to get weeks for. It must be a single cell.

arg2 Range rng: This is the data range excluding headers to count over.

arg3 Long startDateColumn: column number in the selected range containing start date. Not column number in the sheet.

arg4 Long endDateColumn: column number in the selected range containing end date. Not column number in the sheet.

Code goes in a standard module in the VBE project.

Public Function GetWeekCount(ByVal caseNumber As Range, ByVal rng As Range, ByVal startDateColumn As Long, ByVal endDateColumn As Long) As Variant
    Dim arr(), i As Long, j As Long, dict As Object
    If caseNumber.Count <> 1 Then GetWeekCount = CVErr(xlErrValue)
    Set dict = CreateObject("Scripting.Dictionary")
    arr = rng.Value
    For i = LBound(arr, 1) To UBound(arr, 1)
        If arr(i, 1) = caseNumber Then
            For j = CLng(DateValue(arr(i, startDateColumn))) To CLng(DateValue(arr(i, endDateColumn)))
                dict(Application.WorksheetFunction.IsoWeekNum(j)) = 1
            Next j
        End If
    Next i
    If dict.Count > 0 Then
        GetWeekCount = Application.WorksheetFunction.Sum(dict.items)
    Else
        GetWeekCount = 0
    End If
End Function

Example usage:

usage

Standard module (not class and not in sheet pane):

Standard module

QHarr
  • 83,427
  • 12
  • 54
  • 101
  • OK, I'm trying to ge this to work, but keep getting #NAME? In the data sheet, the 'case number' is in column C, and the dates are in columns K:L. I wanted to have the function result in another worksheet (which has a single line of compiled data for each 'Case number' – Natalie Hunt Jul 07 '18 at 10:44
  • Have you put the code in a standard module? You can use it in a different sheet no problem provided the range as second argument includes the sheet name e.g. =GetWeekCount(F1,Sheet3!A1:C6) – QHarr Jul 07 '18 at 10:46
  • I have gone to the VBA project, and selected insert>Module, and put the code there... in the sheet to display the result, I did as you said, and put the range... I'm assuming the range entered, should go from the casenumber to the second date columns in the data sheet...? – Natalie Hunt Jul 07 '18 at 10:52
  • And what has happened? – QHarr Jul 07 '18 at 10:53
  • I did that before I posted my first reply, still have #NAME as the result – Natalie Hunt Jul 07 '18 at 10:53
  • Is it in the right project and definitely not a class module? – QHarr Jul 07 '18 at 10:55
  • in the data sheet, where the information is held, there are several columns between the case number and the dates, if I include the whole range, it would be C:L, with columns D-J containing other data (Including another column with a date) – Natalie Hunt Jul 07 '18 at 10:55
  • I have updated so you can specify which columns hold the start and end date. You shouldn't be getting a Name error though if function is correctly place. You would be getting a different error due to the column gaps. – QHarr Jul 07 '18 at 11:13
  • Sorry for a few bits of the answer not being updated fully between versions. Hopefully all updated above now. – QHarr Jul 07 '18 at 11:27
  • Awesome, thanks, it worked! I have 1 more question, as new records will be added to the data sheet, is there a way to have the formula use the column as a whole rather than a cell number? (I have used L1000 for now) – Natalie Hunt Jul 07 '18 at 21:42
  • What do you mean? You want rng to be dynamic and expand? In which has create a dynamic named range and pass that it. Give me a little more info to help me understand please. :-) – QHarr Jul 08 '18 at 03:59
  • 1
    Currently the number of rows I have is 739, but this will constantly be added to. However, you helped me despite me not explaining well :) I created a dynamic named range for the rng value and inputted that as the rng reference, which worked. Thank You soooo much for your help QHarr! :) – Natalie Hunt Jul 08 '18 at 11:09