Read the correct week number from a calendar date

Question

I have below dates in an Excel column, as you can see.

Sprint 1 takes from 10.04 to 21.04 this means 2 weeks and between brackets they are specified week 15 and 16 which is correct but for Sprint 2, who also starts in 10.04 but takes until 05.05 it means 7 weeks, but are displayed also the weeks from the Sprint1.

"Sprint1 (CW15-16/2017)
[10.04.2017 - 21.04.2017]
Sprint2 (CW15-16/2017)
[10.04.2017 - 05.05.2017]"

What I have until now is:

'reading the first CW of the sprint based on the date
SprintFristCW = Left(planning_wb.Worksheets(SprintPlanningTable).Cells(2, i + 1).Value, 9)

'reading the last CW of the Sprint based on the date
SprintEndCW = Right(planning_wb.Worksheets(SprintPlanningTable).Cells(2, i + Sprintlength).Value, 9)

SprintCW = Left(SprintFirstCW, 4) & "-" & Right(SprintEndCW, 7)

But SprintEndCW is not reading correct the week number.

So I need to read the correct week number in which each sprint ends and print it.

It is very unclear what you are asking. Can you [edit] your question and work it out more clear? Screenshot would help too. And ask a specific question because to answer your question "*Do you think it is possible to do this using a VBA function?*" I will say "*Yes, I think it is possible.*". — Pᴇʜ, Oct 24 '17 at 08:56
Done it, I hope it is clearer now, also added the code that I have until now. — USER7423, Oct 24 '17 at 09:01
No, I'm sorry not really. What is `SprintFirstC` and `SprintEndCW`? Show your whole code or give us a [mcve]. Is this VBA or a formula? We need more details. Imagine we cannot see your sheet and know nothing besides your question. — Pᴇʜ, Oct 24 '17 at 09:02
CW stands for Calendar Week, SprintFirstCW and SprintEndCW shows the week number when the Sprint starts and ends, SprintFirstCW works fine, reads the correct week number but the SprintEndCW do not works. It is VBA, this is the method where I read the week number for the SprintFirstCW: SprintFirstCW = Left(planning_wb.Worksheets(SprintPlanningTable).Cells(2, i + 1).Value, 9) — USER7423, Oct 24 '17 at 09:09
Please **always** [edit] your original question to add code. Code comments are unreadable. Show us your whole `sub`. We don't want to ask for every single line of code. — Pᴇʜ, Oct 24 '17 at 09:10
Ok, sorry, it is my first time when I ask something on stackoverflow, and I am also new to the VBA. The whole sub is huge, I will try to find a way to post it. — USER7423, Oct 24 '17 at 09:14
If it is huge, then please post the relevant part only or best build a [mcve] to show your issue. But to fix your code we need to know what you have done so far. And also a screenshot mostly helps us to understand how your data looks like. — Pᴇʜ, Oct 24 '17 at 09:16

score 4 · Accepted Answer · answered Oct 24 '17 at 11:04

Don't create huge procedures. Small is beautiful. Create functions that feed into your Main procedure. Here is an example. The procedure TestExtraction calls the function ExtractWeeks. Therefore ExtractWeeks needs not be part of the procedure that calls it, making the code easier to understand and maintain.

Private Sub TestExtraction()

    Dim Fun As Long
    Dim DateString As String
    Dim StartDate As Date, EndDate As Date

    DateString = ActiveCell.Value

    ' the DateString is re-defined here for testing purposes
    DateString = "[10.04.2017 - 05.05.2017]"


    Fun = ExtractWeeks(DateString, StartDate, EndDate)
    If Fun < 0 Then
        Debug.Print "Invalid date"
    Else
        With Application
            DateString = "(CW" & .WeekNum(StartDate)
            If Year(StartDate) <> Year(EndDate) Then _
                DateString = DateString & "/" & Year(StartDate)
            DateString = DateString & " - " & .WeekNum(EndDate) & "/" & Year(EndDate) & ")"
        End With
        Debug.Print DateString
        Debug.Print Fun & " weeks"
    End If
End Sub

Private Function ExtractWeeks(ByVal DateString As String, _
                              StartDate As Date, _
                              EndDate As Date) As Long
    ' 24 Oct 2017
    ' return the number of weeks between dates (rounded up)
    ' return -1 if one of the dates is unreadable

    Dim Dates() As String
    Dim i As Integer

    Dates = Split(Mid(DateString, 2, Len(DateString) - 2), "-")
    On Error Resume Next
    For i = 0 To 1
        Dates(i) = Replace(Trim(Dates(i)), ".", Application.International(xlDateSeparator))
    Next i
    StartDate = DateValue(Dates(0))
    EndDate = DateValue(Dates(1))
    If Err Then
        ExtractWeeks = -1
    Else
        ExtractWeeks = Int((StartDate - EndDate) / 7) * -1
    End If
End Function

The point is that not everything that looks like a date is a date Excel can understand. The Function ExtractWeeks converts the "dates' from your worksheet into real dates and returns these dates to the calling procedure. It also returns -1 in case of error which you can use to trap such errors. In my example, the function returns the number of weeks (or -1). You might let it return the CW string my calling procedure constructs. You will find it easy to move the process of constructing that string to the function and let the function return "" in case of error instead of -1. Perhaps you can exclude the possibility of errors in the dates. This is a question of how you integrate the function into your Main.

Read the correct week number from a calendar date

1 Answers1