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



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

Need Help with a formula to calculate a date

  • Filter
  • Time
  • Show
Clear All
new posts

    Need Help with a formula to calculate a date

    I am tracking benefit hours for each week, pay period (2 weeks) and quarter. My problem is that the quarter is not standard. The benefits quarter alternates a 14 week period with a 12 week period. I can only assume the use of alternating weeks is to keep the quarter ending on a pay period date, probably left over from manual calculations.

    I am currently using a table lookup to get the quarter ending date but I would like to just create a field rule which calculates the date.

    This year the problem is further complicated by a full pay period is left in the year after the quarter ends. In this case, an extra pay period (2 weeks) will be added to the quarter to adjust. This means that the 4th quarter will actually be another 14 week period. There will be three consecutive quarters of 14 weeks then quarters will revert to the 14-12 alternating time periods.

    Quarters for 2008
    Qtr 1 12/17/2007 to 03/23/2008 14 weeks (98 days)
    Qtr 2 03/24/2008 to 06/15/2008 12 weeks (84 days)
    Qtr 3 06/16/2008 to 09/21/2008 14 weeks (98 days)
    Qtr 4 09/22/2008 to 12/14/2008 12 weeks (84 days) adjusted to end 12/28/2008 14 weeks (98 days)

    Quarters for 2009
    Qtr 1 12/29/2008 to 04/05/2009 14 weeks
    Qtr 2 04/06/2009 to 06/28/2009 12 weeks
    Qtr 3 06/29/2009 to 10/04/2009 14 weeks
    Qtr 4 10/05/2009 to 12/27/2009 12 weeks

    I need to be able to calculate the Quarter ending date, given any date. I currently calculate the Week_Ending date to search by key.
    In other places, I calculate the Pay_Period ending date, again to do a search by key.
    In the following formulae I use 1/7/1990 as a known Pay_Period ending date.

    DIM my_date AS D
    my_date = {11/17/2008}
    'Calculate Sunday date
    = {11/23/2008}
    ?jtodate(7-(dow_iso(my_date))+ jdate(my_date))
    = {11/23/2008}

    'Calculate Pay Period Ending date
    = {11/30/2008}

    Does anyone have any ideas that will get me pointed in the right direction?

    Thanks in advance,
    Jerry Gray

    Re: Need Help with a formula to calculate a date

    Jerry, if you're looking for a formula that can compute beginning and ending dates for benefit quarters in any arbitrary year, this will not be possible unless the pattern for computing beginning and ending dates for benefit quarters is capable of being defined rigorously. What are the "rules" for computing these dates?


      Re: Need Help with a formula to calculate a date


      Ignoring the adjustment mentioned, the 4 quarters total 364 days, starting the day after the end of the last quarter of the previous year. Qtr_1 contains 98 days, Qtr_2 contains 84 days, Qtr_3 contains 98 days and Qtr_4 contains 84 days. Since the benefit year only consists of 364 days, the extra day in the year forces the dates to creep.

      The following works. There is probably a better way to do it but I can't see it right now. The Known_Qtr_End can be adjusted manually (maybe a global variable) which would allow for this year's adjustment for future dates. The adjustment won't be needed again for about 11 years.

      DIM my_date AS D
      DIM Known_Qtr_End as D
      DIM Qtr_1 as D
      DIM Qtr_2 as D
      DIM Qtr_3 as D
      DIM Qtr_4 as D
      DIM Qtr_Ends as D
      Known_Qtr_End = {1/7/1990}
      My_date = {12/1/2008}

      Qtr_4 = {1/7/1990}+(ROUND_UP((my_date-{1/7/1990})/364,0)*364)
      Qtr_3 = Qtr_4 - 84
      Qtr_2 = Qtr_4 - 182
      Qtr_1 = Qtr_4 - 266
      Qtr_Ends = Case(my_date<=Qtr_1,Qtr_1,my_date<=Qtr_2,Qtr_2,my_date<=Qtr_3,Qtr_3,my_date<=Qtr_4,Qtr_4)
      = {12/14/2008}

      Jerry Gray


        Re: Need Help with a formula to calculate a date

        Jerry, I wonder if something like this might work.

        1) store your "My_date" in a global variable each time the application starts

        2) Move your script into a UDF, with a single "date type" argument

        3) Use a calc field rule expression that calls the UDF and passes the My_date variable to the function

        Don't you suppose that would do the trick?

        FUNCTION Qtr_Ends AS D (Curr_Date AS D )
        	'DIM my_date AS D
        	'DIM Known_Qtr_End as D
        	DIM Qtr_1 as D
        	DIM Qtr_2 as D
        	DIM Qtr_3 as D
        	DIM Qtr_4 as D
        	'DIM Qtr_Ends as D
        	'Known_Qtr_End = {1/7/1990}
        	'My_date = Curr_date
        	Qtr_4 = {1/7/1990}+(ROUND_UP(([COLOR="Red"]Curr_Date[/COLOR]-{1/7/1990})/364,0)*364)
        	Qtr_3 = Qtr_4 - 84
        	Qtr_2 = Qtr_4 - 182
        	Qtr_1 = Qtr_4 - 266
        	Qtr_Ends = Case([COLOR="red"]curr_date[/COLOR]<=Qtr_1,Qtr_1,[COLOR="red"]curr_date[/COLOR]<=Qtr_2,Qtr_2,[COLOR="red"]curr_date[/COLOR]<=Qtr_3,Qtr_3,[COLOR="red"]curr_date[/COLOR]<=Qtr_4,Qtr_4)
        = {12/14/2008}
        Last edited by Tom Cone Jr; 11-22-2008, 10:21 AM. Reason: Show example


          Re: Need Help with a formula to calculate a date


          I think you are right on the money with the UDF.

          My_Date will actually be input from a form to summarize benefit hours for the quarter for reporting purposes. At our casino, employees must work an average of 35 hours per week for the quarter to maintain their company paid benefits. The first time their average drops below 35, they are put on probation until the end of the next quarter. If their average is below 35 for 2 consecutive quarters, they become part time employees and lose their benefits.

          I will add a test to determine if Qtr_4 occurs 14 days or more before the last Pay Period ending date of the year. If the test is true, I will adjust the Known Qtr.

          Thanks for your help,
          Jerry Gray


            Re: Need Help with a formula to calculate a date

            Here is a simple formula for you.
            dim q1_Beging as d={12/29/2008}
            = {03/29/2009}
            = "Sunday"
            = {06/28/2009}
            = "Sunday"
            ..and so on..