Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Number of months in date range ?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    #16
    Re: Number of months in date range ?

    Originally posted by Mike Wilson View Post
    Correct... your posted archive does not generate the value that Tom was looking for.

    for date1 as D={11/09/2006} and date2 as D={11/17/2008} the answer is 25. Neither of yours produce that.
    So why wouldn't someone take the accurate # like
    ?agemonths({11/09/2006},{11/17/2008})
    = 24.2581
    and then apply a INT(), ROUND() or ROUND_UP() function to get the needs of the specific usage, like
    ?round_up(agemonths({11/09/2006},{11/17/2008}),0)
    = 25
    Originally posted by Mike Wilson View Post
    Also trying to fit into Tom's words of... "Was the quickest for me to grasp. "
    Except for the last 2 lines of my code that is relatively complicated, the meat of the function is very easy (You have to ignore all the data checks, default values etc adjustments at the top). But I do understand the need to comprehend the complexities of a problem.

    The negative months issue (where the absolute value going back from a base date is not necessarily the same as reversing two parameters) is probably the hardest issue there.
    Regards,

    Ira J. Perlow
    Computer Systems Design


    CSDA A5 Products
    New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
    CSDA Barcode Functions

    CSDA Code Utility
    CSDA Screen Capture


    Comment


      #17
      Re: Number of months in date range ?

      A mountain was made out of a molehill and for a good reason.
      The molehill:
      Re-reading Tom's question, the answer is actually much simpler than all the strenous responses made, mine included.
      Tom is asking: I have a time period and I want to take each month in this time period, be it a full month or part of the month, and enter that in an array. Tom evidently stumbled at how to size the array. He wants to know how many months are there in this period so as to size the array.
      1-For a starter, you do not need to size the array from the start. You could start with an empty array and add to it. Typically that is not my preferred way if you are dealing with a large array, however it is hard to believe that Tom is dealing with 1000's of months unless he is keeping records for Moses. So, starting with an empty array is not a bad idea in this scenario.
      2-But assume that you still want to know the number of months for whatever reason, again in this scenario the answer is very simple:
      Num_of_months=12*(year(end)-year(start))+month(end)-month(start)+1
      Code:
      d1={1/10/09}
      d2={2/15/09}
      ?12*(year(d2)-year(d1))+month(d2)-month(d1)+1
      = 2
      The mountain:
      I am guilty of this. I was answering an imaginary question. Not exactly imaginary but I believe the question was asked before.
      You could envision several variations of Tom's question which could make this a bit of an exercise:
      What if you want to calculate just the "Full months" lapsed between 2 dates?
      Or:
      What if you want to calculate the "number of months" lapsed between the 2 dates?
      I am not going to explain what I mean by the latter nor answer any of these questions because for one thing that will take this thread off track and more importantly the answer really depends on what is it exactly you mean not to mention, it's Monday morning and time is tight. I remember writing a thread about the hidden pitfall of addmonths(). Just to wet your appetite (and if you wish more elaboration perhaps a new thread is in order):
      What is the correct answer to:
      addmonths({2/28/10},1)
      ??

      Comment


        #18
        Re: Number of months in date range ?

        G, your expression is straightforward and easy to understand. Thanks.

        -- tom

        Comment


          #19
          Re: Number of months in date range ?

          Code:
          What is the correct answer to:
          addmonths({2/28/10},1)
          G., With a filosophical approach one would add 1 month to the given date. So let 's lay down {3/28/10}

          All,

          I had to think about this.
          Someone wanted to know how's his target was going. As there is just one day in the month you can answer that correct, redefine the past time.
          Don't wait untill the end of the month. Just re-period. If it's the 17th today, calculate from the 17th of last mont untill yesterday and report that as last months result.

          It's a different approach but can work well. With that result over the past 'month' you can show how it's going. Insight most of the times inspires better than an usual (montlhy annoying) overview. An you can do it whenever you want.
          One can see his progress better than comparing a month budget with figures from 1 till 16th. You'll never hear:"I don't know, this are just 2 weeks and I need a month" or after some days with the regular overview, "we started already a new month".

          Try, it works

          Ton
          Most things are simple but unfortunately only after the first time

          Comment


            #20
            Re: Number of months in date range ?

            fudgie = 364.25/12 = 30.25 actually 30.354167
            dbgin = {12/18/2009}
            dend = {08/26/2010}
            ?round((jdate(dend) - jdate(dbgin))/30.35,0)
            =8
            ?round((jdate(dend) - jdate(dbgin))/30.35,2)
            = 8.27
            =====================================
            dbgin = {12/18/2009}
            dend = {01/15/2010}
            ?round((jdate(dend) - jdate(dbgin))/30.35,0)
            =1
            ?round((jdate(dend) - jdate(dbgin))/30.35,2)
            = .59
            =====================================
            dbgin = {12/18/2009}
            dend = {01/05/2011}
            ?round((jdate(dend) - jdate(dbgin))/30.35,0)
            =13
            ?round((jdate(dend) - jdate(dbgin))/30.35,2)
            = 12.62
            =====================================
            dbgin = {12/18/1999}
            dend = {12/05/2000}
            ?round((jdate(dend) - jdate(dbgin))/30.35,0)
            = 12
            ?round((jdate(dend) - jdate(dbgin))/30.35,2)
            = 11.63
            Originally posted by Tom Cone Jr View Post
            A puzzler.

            Assume any arbitrary beginning date.
            Assume any arbitrary ending date.
            Assume the ending date will be "after" the beginning date.

            What's the most efficient way to compute the number of calendar months (full or partial) that are present in the given range?

            Background: Have a need to aggregate sales and payment data by calendar month, across any arbitrary date range. Beginning and ending dates may or may not be the first or last of a month. They may or may not be in the same calendar year. I want to populate an array that contains an element for each month (or partial month) in the date range. I need to know how to size my array, so I need to know the number of full or partial months in the date range.

            Enjoy.

            -- tom
            Last edited by SMARTII; 01-04-2010, 08:23 PM.
            First Love

            Comment


              #21
              Re: Number of months in date range ?

              Tom,
              Nice code but none of what your code develops are the correct answer for Tom Cone. dbgin = {12/18/2009} dend = {08/26/2010} answer needs to be 9, not 8
              Mike W
              __________________________
              "I rebel in at least small things to express to the world that I have not completely surrendered"

              Comment


                #22
                Re: Number of months in date range ?

                Originally posted by Mike Wilson View Post
                Tom,
                Nice code but none of what your code develops are the correct answer for Tom Cone. dbgin = {12/18/2009} dend = {08/26/2010} answer needs to be 9, not 8
                If you mean that the fraction must always round up? to get the needed array elements??
                OK,
                dbgin = {12/18/2009}
                dend = {08/26/2010}

                ?round_UP((jdate(dend) - jdate(dbgin))/30.35,0)
                = 9
                =====================================
                dbgin = {12/18/2009}
                dend = {12/19/2009}
                ?round_UP((jdate(dend) - jdate(dbgin))/30.35,0)
                = 1

                I use to use:
                Option base 0
                but I can't find it in xbasic.

                R we there yet?
                Last edited by SMARTII; 01-04-2010, 11:13 PM.
                First Love

                Comment


                  #23
                  Re: Number of months in date range ?

                  very nice and simple alternative - best one yet, I think
                  Cole Custom Programming - Terrell, Texas
                  972 524 8714
                  [email protected]

                  ____________________
                  "A young man who is not liberal has no heart, but an old man who is not conservative has no mind." GB Shaw

                  Comment


                    #24
                    Re: Number of months in date range ?

                    Tom, I'm curious. Where does the 30.35 come from?

                    Comment


                      #25
                      Re: Number of months in date range ?

                      Tom C:
                      I believe Tom M is using 30.25 as an average days per month, something you should never do and sorry Tom M, we are not there yet and we will never be there using average and rounding. Wrong concept, bad practice.
                      Code:
                      dbgin = {12/18/1910}
                      dend = {08/26/2010}
                      ?round_UP((jdate(dend) - jdate(dbgin))/30.35,0)
                      = 1200 [COLOR="Red"]'wrong answer[/COLOR]
                      ?12*(year(dend)-year(dbgin))+month(dend)-month(dbgin)+1
                      = 1197 [COLOR="SeaGreen"]'correct answer[/COLOR]
                      To illustrate it further and make simpler to comprehend:
                      Code:
                      dbgin = {12/18/1910}
                      dend = {12/18/2010}
                      ?round_UP((jdate(dend) - jdate(dbgin))/30.35,0)
                      = 1204 [COLOR="Red"]'wrong answer[/COLOR]
                      ?12*(year(dend)-year(dbgin))+month(dend)-month(dbgin)+1
                      = 1201 [COLOR="SeaGreen"]'correct answer[/COLOR]
                      Last edited by G Gabriel; 01-05-2010, 03:02 AM.

                      Comment


                        #26
                        Re: Number of months in date range ?

                        Originally posted by Tom Cone Jr View Post
                        Tom, I'm curious. Where does the 30.35 come from?
                        That comes from an old indian calculation of the "many moons"
                        turns out to be:
                        364.25 days per yr. / 12mo.
                        = 30.25 actually 30.354167

                        Just not as many moons as GG had in mind.
                        First Love

                        Comment


                          #27
                          Re: Number of months in date range ?

                          another "cross check" on:
                          dbgin = {12/18/1910}
                          dend = {12/18/2010}
                          :
                          ?addmonths({12/18/1910},1200)
                          = {12/18/2010}
                          At what month does it no longer remain accurate?
                          Any idea?
                          Seems OK for 26 years IF you are using it for the max array needed.
                          Otherwise just use without the round_up,0
                          dbgin = {12/18/1984}
                          dend = {12/18/2010}
                          ?round_up((jdate(dend) - jdate(dbgin))/30.35,1)
                          = 312.9
                          ?addmonths({12/18/1984},312.9)
                          = {12/18/2010}
                          But then someone said F=MA was negated by E=MC (sq) beyond a certain point.
                          But then someone else told me E=MC (sq) falls apart at a certain point.
                          But then F=MA still works on a Stealth Fighter in an after burner dive.
                          That is, at less than what extreme is simplicity adequate for the intended purpose?
                          Originally posted by G Gabriel View Post
                          Tom C:
                          I believe Tom M is using 30.25 as an average days per month, something you should never do and sorry Tom M, we are not there yet and we will never be there using average and rounding. Wrong concept, bad practice.
                          Code:
                          dbgin = {12/18/1910}
                          dend = {08/26/2010}
                          ?round_UP((jdate(dend) - jdate(dbgin))/30.35,0)
                          = 1200 [COLOR=red]'wrong answer[/COLOR]
                          ?12*(year(dend)-year(dbgin))+month(dend)-month(dbgin)+1
                          = 1197 [COLOR=seagreen]'correct answer[/COLOR]
                          To illustrate it further and make simpler to comprehend:
                          Code:
                          dbgin = {12/18/1910}
                          dend = {12/18/2010}
                          ?round_UP((jdate(dend) - jdate(dbgin))/30.35,0)
                          = 1204 [COLOR=red]'wrong answer[/COLOR]
                          ?12*(year(dend)-year(dbgin))+month(dend)-month(dbgin)+1
                          = 1201 [COLOR=seagreen]'correct answer[/COLOR]
                          Last edited by SMARTII; 01-05-2010, 04:32 AM.
                          First Love

                          Comment


                            #28
                            Re: Number of months in date range ?

                            Now my seemingly simple question:
                            addmonths({2/28/10},1) doesn't seem "Filosphical" any more nor the answer is seemingly obvious either. is it?

                            Comment


                              #29
                              Re: Number of months in date range ?

                              Originally posted by G Gabriel View Post
                              Now my seemingly simple question:
                              addmonths({2/28/10},1) doesn't seem "Filosphical" any more nor the answer is seemingly obvious either. is it?
                              ?addmonths({2/28/10},1)
                              = {03/28/2010}
                              =======================
                              dbgin = {02/28/2010}
                              dend = {03/28/2010}
                              ?round_up((jdate(dend) - jdate(dbgin))/30.35,0)
                              = 1
                              I'm lost as usual.
                              I need a Cowboy helmet with a green dot.
                              For some reason, approximations don't bother me as long as they give the right answer to my problem range.
                              ==================================
                              for instance, this is something I use every day for 100 clients:
                              dend = {12/28/2009}
                              ?month(addmonths(dend,11.066))
                              = 11

                              ?year(addmonths(dend,11.066))
                              = 2010

                              11/2010
                              A license is issued for 12 months with the issue month being month 1 no matter the day, and the expiration date being the last day of month 12.
                              When does it expire?
                              ans: 11/30/2010.
                              They actually got cheated out of nearly a month because month 1 is on its last few days.
                              11.066 works here; whether or not it would work for a 30 yr license I have no idea.
                              Last edited by SMARTII; 01-05-2010, 12:05 PM.
                              First Love

                              Comment


                                #30
                                Re: Number of months in date range ?

                                Originally posted by SMARTII View Post
                                That comes from an old indian calculation of the "many moons"
                                turns out to be:
                                364.25 days per yr. / 12mo.
                                = 30.25 actually 30.354167
                                It seems that this is where the problem is. There are roughly 365.25 days per year. That translates to:
                                Code:
                                ?365.25/12
                                = 30.437500
                                So by using 30.44, we have:
                                Code:
                                ?round_UP((jdate(dend) - jdate(dbgin))/30.44,0)
                                = 1197.000000

                                Comment

                                Working...
                                X