VB Loop - All posible combinations into an array

Hi,
Im trying to write some results into an array for working out all possible combinations of days, weeks and months, between 2 dates.

For exmaple, between 1st Jan and the 20th jan, i want to know all combinations of days, weeks, months you could have

Total Days = 21
Day = 1
Week = 7
Month = 31

Combinations to an array, should be something like:
1 = 21day, 0 Weeks, 0 Months
2 = 14day, 1 week,0 months
3 = 7day, 2 week,0 months
4 = 0day, 3 week,0 months
sub CalCombinations()
 
StartDate = "1/01/2009"
EndDate = "31/01/2009"
 
TotalDays = DateDiff("d", StartDate, EndDate) + 1 'add 1 day to cover for the start day
TotalFullWeeks = DateDiff("w", StartDate, EndDate)
TotalFullMonths = DateDiff("m", StartDate, EndDate)
 
End sub

Open in new window

jamiepryerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DhaestConnect With a Mentor Commented:
I made some assumptions:
- A month has 4 weeks
- A month has 30 days (average)

    Dim possibleCombinations() As String
    ReDim possibleCombinations(0)
    StartDate = "1/01/2009"
    EndDate = "28/02/2009"
    
    totalDays = DateDiff("d", StartDate, EndDate) + 1 'add 1 day to cover for the start day
    TotalFullWeeks = DateDiff("w", StartDate, EndDate)
    totalFullMonths = DateDiff("m", StartDate, EndDate)
 
    Dim strToAdd As String
    For j = 0 To totalFullMonths
        For i = 0 To (TotalFullWeeks + (totalFullMonths * 4))
            If (totalDays - ((j * 30) + (i * 7))) > 0 Then
                strToAdd = (totalDays - ((j * 30) + (i * 7))) & " days, " & i & " Weeks, " & j & " Months"
                possibleCombinations(UBound(possibleCombinations)) = strToAdd
                ReDim Preserve possibleCombinations(UBound(possibleCombinations) + 1) As String
            End If
        Next i
    Next j
    ReDim Preserve possibleCombinations(UBound(possibleCombinations) - 1) As String

Open in new window

0
 
jamiepryerAuthor Commented:
thanks, that works really well
can you just talk me through your thinking for the

"    For I = 0 To (TotalFullWeeks + (totalFullMonths * 4))"

and

"If (totalDays - ((j * 30) + (I * 7))) > 0 Then"

please..
0
 
DhaestCommented:
1. For I = 0 To (TotalFullWeeks + (totalFullMonths * 4))"
We need to loop for each week (but a month contains 4 weeks, that's why you have
"number of weeks" + ("number of months" * 4 = number of weeks for these months)

2. If (totalDays - ((j * 30) + (I * 7))) > 0 Then
j represents a month (so x-monts * 30 days to have the number of days
l represents a week (so x-weeks * 7 days) to have the number of days

Totaldays is a number that gives a an number of days between start and end.
When we have for example: 1 week and 1 day -> we'll have also the possibility to have 8 days
If we don't use formula above, we'll get the following result
1 week - 1 days
0 week - 8 days
0 week - 1 days
The last is incorrect off-course
0
 
jamiepryerAuthor Commented:
thanks!
0
 
DhaestCommented:
Glad I could help you...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.