Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    How many Tuesdays in a range of dates?

    I have a highly-evolved workbook used for keeping track of many aspects of a 100-person Powerball and Mega Millions lottery pool that I run for friends and acquaintances. Many of that workbook's best features have come from answers to questions posted here, so thanks to all for their help so far. Now I need something else.

    The lottery pools run from date X to date Y, usually about 13 weeks apart. During the period between those dates, there are lottery drawings every Tuesday, Wednesday, Friday, and Saturday. I need a way to tell how many drawings, total, take place between date X and date Y.

    The idea is there will be three cells in a row on a worksheet. Cell #1 will contain the start date (date X), cell #2 will contain the end date (date Y), and cell #3 will contain the total number of drawings during the pool. My problem is how to get cell #3 to do what I want it to.

    It looks to me like some sort of user-defined function might be useful here, and might even already exist. (I've dabbled in these, but it's been a LONG time since I last touched them.) Arguments of the function could be start date and end date. The function could return seven values (can functions do that?): Number of Sundays between start date and end date, number of Mondays, number of Tuesdays, etc.

    Any sort of guidance, solution, etc. will be greatly appreciated. (And if we ever win a Jackpot, we will certainly make a BIG donation to this forum).
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Lou,

    Here's a UDF that will do the trick.
    Code:
    Option Explicit
    
    Public Function LotteryDays() As Integer
    
      Dim iCntOfdays     As Integer
      Dim iDayOfWeek     As Integer
      Dim iCntr          As Integer
      Dim dBaseDate      As Date
      
      
      iCntOfdays = ([B1] - [A1]) + 1
      dBaseDate = [A1]
      
      For iCntr = 1 To iCntOfdays
         iDayOfWeek = WorksheetFunction.Weekday(dBaseDate + iCntr)
         Select Case iDayOfWeek
            Case 3, 4, 6, 7
              LotteryDays = LotteryDays + 1
         End Select
      Next iCntr
      
    End Function
    Attached is an .xlsm file that has the data I used to test the function.

    I'm sure someone will come up with a formula that fits in a cell but if not you have this as a fallback.
    Attached Files Attached Files
    Last edited by RetiredGeek; 2012-02-14 at 19:27. Reason: Steve's post made me realize my numbers were 1 off!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Lou Sander (2012-03-15)

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If the start date is in A1, the enddate is in A2 you can use the formula:
    =INT(($A$2-($A$1+3-WEEKDAY($A$1)+7*(3<WEEKDAY($A$1))))/7)+1

    to calculate the number of Tuesday (weekday = 3). which answers the question posed in the title. (Of course you can enter the dates in the formula instead of the cell references if desired.

    To get the sums of the Tues, Wed, Fri, and Sat in the range (Weekdays = 3,4,6,7) you can use the array formula (confirm with ctrl-shift-enter):
    =SUM(INT(($A$2-($A$1+WEEKDAY({3,4,6,7})-WEEKDAY($A$1)+7*(WEEKDAY({3,4,6,7})<WEEKDAY($A$1)) ))/7)+1)

    Steve

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Steve,

    See I told you someone {I knew it would be you but didn't want to put any pressure on} would do it. I tried it out on my test sheet but got an error then I realized that you interpreted "in a row" to be down a column where I interpreted it to be across a row. Simply changing the $A$2 to $B$1 fixed it nicely. BRAVO Steve!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hi Again All,

    Here's a New and Improved UDF allowing the caller to specify the cells holding the beginning & ending dates and a string containing the list of days-of-week to be counted. If the days-of-week string is ommited it will default to 1 or Sundays.

    Calling Example for thread example: =LotteryDays(A1,B1,"3467")
    Code:
    Option Explicit
    
    Public Function LotteryDays(dteStart As Date, dteEnd As Date, Optional zDOWs As String) As Integer
    
      Dim iCntOfdays     As Integer
      Dim iDayOfWeek     As Integer
      Dim iCntr          As Integer
      
      If IsMissing(zDOWs) Then zDOWs = "1"
      iCntOfdays = (dteEnd - dteStart) + 1
      
      For iCntr = 1 To iCntOfdays
         iDayOfWeek = WorksheetFunction.Weekday(dteStart + iCntr)
         Select Case InStr(zDOWs, Format(iDayOfWeek, "#"))
            Case Is > 0
              LotteryDays = LotteryDays + 1
         End Select
      Next iCntr
      
    End Function
    Last edited by RetiredGeek; 2012-02-15 at 07:09.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Lou Sander (2012-03-15)

  8. #6
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    This is great! I will play with it tomorrow. In the meantime, I have to figure out how to get this forum to default to sending me email whenever there is a reply posted. I've sat here since my original post, waiting for that email. Sheesh!

    I'm thinking that in the provided example that changing

    If IsMissing(zDOWs) Then zDOWs = "1"

    to

    If IsMissing(zDOWs) Then zDOWs = "1234567"

    the function would just return the number of days between dteStart and dteEnd. Am I right?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  9. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    The SPAM above has been reported to the Forum moderators and has been removed
    Last edited by MartinM; 2012-02-18 at 11:36.

  10. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by Lou Sander View Post
    This is great! I will play with it tomorrow. In the meantime, I have to figure out how to get this forum to default to sending me email whenever there is a reply posted. I've sat here since my original post, waiting for that email. Sheesh!

    I'm thinking that in the provided example that changing

    If IsMissing(zDOWs) Then zDOWs = "1"

    to

    If IsMissing(zDOWs) Then zDOWs = "1234567"

    the function would just return the number of days between dteStart and dteEnd. Am I right?

    Lou,

    Yes, you are correct.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,830
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Steve

    A lovely formula!

    Re: To get the sums of the Tues, Wed, Fri, and Sat in the range (Weekdays = 3,4,6,7) you can use the array formula (confirm with ctrl-shift-enter):
    =SUM(INT(($A$2-($A$1+WEEKDAY({3,4,6,7})-WEEKDAY($A$1)+7*(WEEKDAY({3,4,6,7})<WEEKDAY($A$1)) ))/7)+1)

    I notice that when arrays such as {3,4,6,7} are used directly in formulas, you don't need to use ctrl-shift-enter, but if you refer to a cell range containing the elements, then you must use ctrl-shift-enter.
    So your formula will work either way.
    I suppose it is always safer to use ctrl-shift-enter, but do you know if there are any advantages to NOT using??

    zeddy

  12. The Following User Says Thank You to zeddy For This Useful Post:

    sdckapr (2012-02-16)

  13. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Thanks, I hadn't noticed the issue with directly entered arrays vs indirect arrays with ranges (I don't use them that much). I suppose that the ctrl-shift-enter is to trigger/translate that the range is an array. Some "array formulas" (like sumproduct) implicitly presume they are arrays and thus they don't need the ctrl-shift-enter either...

    I can't think of any advantage to use it or not use it, though I suppose there could be a little more "overhead" as an explicit array. I would worry more about the general use of the array formulas and the number of calculations they may represent that may be recalculated with every entry.

    Steve

  14. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,830
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Steve

    I think the number of calcs would be the same either way.
    When I want to check complicated formulas I often use the [F9] trick in the formula bar, for example, in your formula, if I highlight just the section WEEKDAY({3,4,6,7})-WEEKDAY($A$1) within the formula bar, and then press [F9], it shows me the results {0,1,3,4} for this part of the calc.

    I guess I would continue to play safe and use Ctrl-Shift-Enter whenever any array ranges or direct array elements are used in formulas.

    I always find your thoughts very useful.
    Many thanks

    zeddy

  15. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I agree, there is no difference with or without the ctrl-shift-enter. All that does is explicitly tell excel that the formula is an array formula. Some formulas it can tell implicitly. My point about the "general use" was more an aside where people try to use them as a substitute/mimic for something like a pivot table and in those cases it can use much overhead

    Steve

  16. #13
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Sorry it has taken me so long to implement this and report back. Believe it or not, sometimes things come up that are (or seem to be) more important than spreadsheet development. Sigh!!!

    I've used Retired Geek's improved UDF. (The other stuff was too complicated with the CTRL+Enter business.)

    There's a flaw, probably easily corrected, but I don't see how to do it myself:

    When one appies the UDF to various date ranges, one discovers that Sunday is day 2, not day 1; Monday is day 3, not day 2, etc.

    In other words, the function works, but the numbers used for the days are off by one from what would be expected. Try it on April, 2012, and see what I mean.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  17. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,830
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Lou

    First of all, can you please give us a list of all the things that are more important than spreadsheet development.

    Secondly, what day do you want us to try it on? You said "Try it on April, 2012".

    For the weekday function, Sunday should be 1, Monday 2 etc.

    zeddy
    Last edited by zeddy; 2012-03-12 at 05:51.

  18. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,830
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Lou

    Try this amended code:

    Code:
    Public Function LotteryDays(dteStart As Date, dteEnd As Date, Optional zDOWs As String) As Integer
      Dim iCntOfdays     As Integer
      Dim iDayOfWeek     As Integer
      Dim iCntr          As Integer
      
      If IsMissing(zDOWs) Then zDOWs = "1"
      iCntOfdays = (dteEnd - dteStart)
      
      For iCntr = 0 To iCntOfdays
         iDayOfWeek = WorksheetFunction.Weekday(dteStart + iCntr)
         Select Case InStr(zDOWs, Format(iDayOfWeek, "#"))
            Case Is > 0
              LotteryDays = LotteryDays + 1
         End Select
      Next iCntr
      
    End Function
    I believe the previous code would 'miss' the start date, because it 'added 1' to it in the For loop.

    zeddy
    Last edited by zeddy; 2012-03-12 at 06:16. Reason: got my slash code wrong

  19. The Following User Says Thank You to zeddy For This Useful Post:

    Lou Sander (2012-03-15)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •