Vba Maths Challenge/Problem - Offbeat Weekday Patterns

[ClimoC]

I'm sure this might just be 2 integers working cleverly in tandem, but I'm not 100% it would work for ANY instance...

Scenario:
Userform with 3 pertinent sections -

A) A 'Starting Date' field (Date)
B) A 'Number of Dates' (Long/Byte)
C) 7 Checkboxes, 1 for each day of the week

If a user selects only "Monday", and says "10 Dates", you could very easily just count through a loop adding 7 to the 'Starting Date'

But we need something that means you could Check "Monday" & "Wednesday", enter "6 dates", and wind up with these in cells:

A1 : 03/01/2011
A2 : 05/01/2011
A3 : 10/01/2011
A4 : 12/01/2011
A5 : 17/01/2011
A6 : 19/01/2011

or if the user selected Monday, Tues, Wed, Thur & Fri, and said 20 dates, you'd get:

A1 : 03/01/2011
A2 : 04/01/2011
A3 : 05/01/2011
A4 : 06/01/2011
A5 : 07/01/2011
A6 : 10/01/2011
A7 : 11/01/2011
A8 : 12/01/2011.... etc

basically, populate as many dates as there are 'dates' to enter, based on the pattern set forth by the checkboxes in the userform?

I'm going to put a handle on the form so that the 'Start Date' HAS to be the 'first checked box running Monday - Sun' so you can't enter the 03/01/2011 (A monday) as the 'start date', if the first checkbox to be true is a Wednesday...

Any advice or a nudge in the right direction would be greatly appreciated!

Thanks
C

 

[shg]

This would essentially duplicate the Excel 2010 Workday.Intl function?

http://office.microsoft.com/en-us/starter-help/workday-intl-function-HA010354380.aspx

 

[ClimoC]

Ah. Yes, it looks like it would... I could then pass my checkbox values from True/False into the prescribed pattern for the function...

But For 2003?... We still have a lot of machines on 2003, and this will be running as a userform because it takes the fields in the userform to essentially make a little Database in one of the sheets...

I don't have to initialise it in a loop...necessarily...I don't think...!

 

[ClimoC]

Ah. Yes, it looks like it would... I could then pass my checkbox values from True/False into the prescribed pattern for the function...

But For 2003?... We still have a lot of machines on 2003, and this will be running as a userform because it takes the fields in the userform to essentially make a little Database in one of the sheets...

I don't have to initialise it in a loop...necessarily...I don't think...!

I should also point out that whatever loop I initialise needs to handle anywhere weekday-pattern, not just '2days off' as I think the Workday.intl function does. If I say MTThS, then that has to be a pattern too...

I've started down another path unless someone can suggest something else, I was thinking about adding all the days 'ticked' from the checkboxes to a scripting dictionary, then passing through every day of the week, if it exists, then enter that date forward from the most recent date created, until it runs out of 'number of days' to create?

Thoughts?

 

[tusharm]

Just "brute force" the solution.

Here's some psuedu code:

DesiredDays={convert the checkboxes to a 7 element array of true/false values}
nbrdates=clng(me.nbrdates.value)
aDate=cdate(me.startdate.value)
do while nbrdates>0
    if desiredDays(weekday(adate)) then
        nbrdates=nbrdates-1
        write adate to whereever
        end if
    adate=adate+1
    loop

I'm sure this might just be 2 integers working cleverly in tandem, but I'm not 100% it would work for ANY instance...

Scenario:
Userform with 3 pertinent sections -

A) A 'Starting Date' field (Date)
B) A 'Number of Dates' (Long/Byte)
C) 7 Checkboxes, 1 for each day of the week

If a user selects only "Monday", and says "10 Dates", you could very easily just count through a loop adding 7 to the 'Starting Date'

But we need something that means you could Check "Monday" & "Wednesday", enter "6 dates", and wind up with these in cells:

A1 : 03/01/2011
A2 : 05/01/2011
A3 : 10/01/2011
A4 : 12/01/2011
A5 : 17/01/2011
A6 : 19/01/2011

or if the user selected Monday, Tues, Wed, Thur & Fri, and said 20 dates, you'd get:

A1 : 03/01/2011
A2 : 04/01/2011
A3 : 05/01/2011
A4 : 06/01/2011
A5 : 07/01/2011
A6 : 10/01/2011
A7 : 11/01/2011
A8 : 12/01/2011.... etc

basically, populate as many dates as there are 'dates' to enter, based on the pattern set forth by the checkboxes in the userform?

I'm going to put a handle on the form so that the 'Start Date' HAS to be the 'first checked box running Monday - Sun' so you can't enter the 03/01/2011 (A monday) as the 'start date', if the first checkbox to be true is a Wednesday...

Any advice or a nudge in the right direction would be greatly appreciated!

Thanks
C

 

[shg]

Another way:

       ----A----- -------B------- -------C-------
   1   Start Date Mon 14 Mar 2011 Mon 14 Mar 2011
   2         nDay              10              10
   3     Workdays 0000011         1010101        
   4                                             
   5              Mon 14 Mar 2011 Tue 15 Mar 2011
   6              Tue 15 Mar 2011 Thu 17 Mar 2011
   7              Wed 16 Mar 2011 Sat 19 Mar 2011
   8              Thu 17 Mar 2011 Tue 22 Mar 2011
   9              Fri 18 Mar 2011 Thu 24 Mar 2011
  10              Mon 21 Mar 2011 Sat 26 Mar 2011
  11              Tue 22 Mar 2011 Tue 29 Mar 2011
  12              Wed 23 Mar 2011 Thu 31 Mar 2011
  13              Thu 24 Mar 2011 Sat 02 Apr 2011
  14              Fri 25 Mar 2011 Tue 05 Apr 2011
  15                                             
  16                                             
  17                                             
  18                                             
  19                                             
  20                                             
  21                                             
  22                                             
  23                                             
  24

The array formula in B5:B24 is

=TRANSPOSE(WorkdaysIntl(B1, B2, B3))

That's copied to C5:C24.

Note that B3 and C3 are strings.

The UDF should (untested) also work in VBA:

Function WorkdaysIntl(ByVal dBeg As Date, _
                      nDay As Long, _
                      ByVal sWorkDays As String) As Variant
    ' shg 2011
    ' UDF or VBA
 
    ' Returns an array of nDay workdays, starting with the first workday
    ' after dBeg
 
    ' sWorkdays is a 7-character string containing only 1s and 0,
    ' where 0 indicates a workday, starting on Monday
 
    Dim avOut       As Variant
    Dim iDay        As Long
    Dim iPos        As Long
 
    If Len(Replace(sWorkDays, "0", "")) + Len(Replace(sWorkDays, "1", "")) <> 7 Or _
       sWorkDays = "1111111" Or nDay < 1 Then
        WorkdaysIntl = CVErr(xlErrValue)
        Exit Function
    End If
 
    If TypeOf Application.Caller Is Range Then
        With Application.Caller
            If .Rows.Count > 1 And .Columns.Count > 1 Then
                WorkdaysIntl = CVErr(xlErrValue)
            Else
                ReDim avOut(1 To .Cells.Count)
            End If
        End With
    Else
        ReDim avOut(1 To nDay)
    End If
 
    sWorkDays = sWorkDays & sWorkDays
    iPos = InStr(Weekday(dBeg, vbMonday), sWorkDays, "0")
 
    ' get the days
    Do While iDay < nDay
        dBeg = dBeg + iPos - Weekday(dBeg, vbMonday)
        iDay = iDay + 1
        avOut(iDay) = dBeg
        iPos = InStr(Weekday(dBeg, vbMonday) + 1, sWorkDays, "0")
    Loop
 
    ' if UDF, blank-fill any unused portion of array
    For iDay = nDay + 1 To UBound(avOut)
        avOut(iDay) = vbNullString
    Next iDay
 
    WorkdaysIntl = avOut
End Function

 

[ClimoC]

Just "brute force" the solution.

Here's some psuedu code:

DesiredDays={convert the checkboxes to a 7 element array of true/false values}
nbrdates=clng(me.nbrdates.value)
aDate=cdate(me.startdate.value)
do while nbrdates>0
    if desiredDays(weekday(adate)) then
        nbrdates=nbrdates-1
        write adate to whereever
        end if
    adate=adate+1
    loop

THanks, I was hoping not to have to brute force it, but this works fast enough when I combined with a collection for use of the exists statement.

'Shame that you can't seem to get a 2003/2007 equivalent add-in for the new 2010 functions...

 

[tusharm]

Why not brute force? Elegance is all nice and good but when the straightforward way is both fast enough and easy enough to understand, it becomes the best way to go, especially when it also means the min. effort testing/debugging the code.

As far as 2010 functions go, if MS retrofitted earlier versions with all the latest bells and whistles, it might seriously cut down on upgrade purchases. {grin}

THanks, I was hoping not to have to brute force it, but this works fast enough when I combined with a collection for use of the exists statement.

'Shame that you can't seem to get a 2003/2007 equivalent add-in for the new 2010 functions...

 

[ClimoC]

Why not brute force? Elegance is all nice and good but when the straightforward way is both fast enough and easy enough to understand, it becomes the best way to go, especially when it also means the min. effort testing/debugging the code.

As far as 2010 functions go, if MS retrofitted earlier versions with all the latest bells and whistles, it might seriously cut down on upgrade purchases. {grin}

Hehe too true... But I'd be much happier to switch to 2010 if I could find where the put everything, and if my GUI interference wasn't thrown off into weird sub-sections of the header menus... sigh... I guess you gotta upgrade sometime...