Calculating the first and last date in a quarter - Forum - OpenEdge Development - Progress Community

Calculating the first and last date in a quarter

 Forum

Calculating the first and last date in a quarter

This question is answered

Hi Would anyone be able to point me in the direction of how to calculate the first and last date in a quarter  based on any given date as a param

I can get the first and last date of a month using 

ASSIGN dtFirstOfMonth = DATE(MONTH(dtDate),1,YEAR(dtDate))

dtLastOfMonth = dtFirstOfMonth + 31 - DAY(dtFirstOfMonth + 31).

but not sure how i would get a full quarter based on any given date ?

Verified Answer
  • If you can do it in Excel you can certainly do it in ABL:

    def var i_dt as date no-undo initial 3/31/2018.
    
    def var dtstart as date no-undo.
    def var iquarter as int no-undo.
    
    assign 
       iquarter = truncate( ( month( i_dt ) - 1 ) / 3, 0 ) + 1   .
       dtstart = date( ( iquarter - 1 ) * 3 + 1, 1, year( i_dt ) )
       .
    
    message
       iquarter skip 
       iso-date( dtstart ) skip
       iso-date( add-interval( dtstart, 3, "months" ) - 1 )
    view-as alert-box.   
       
    
All Replies
  • dtFirstOfMonth = dtDate - (day(dtDate) - 1).

    dtLastOfMonth = add-interval(dtFirstOfMonth,1,"month") - 1.

    That's probably a more elegant solution.

    How would you work out the first and last dates in a quarter on paper? I'm asking because the term 'quarter' seems to vary from country to country. Also, working out how you would do it on paper is a good step to solving it in code.

  • If i was doing it in excel i would use this for the first date

    =DATE(YEAR(A2),FLOOR(MONTH(A2)-1,3)+1,1)

    and for the last date i would use

    =DATE(YEAR(A2),((INT((MONTH(A2)-1)/3)+1)*3)+1,1)-1

  • If you can do it in Excel you can certainly do it in ABL:

    def var i_dt as date no-undo initial 3/31/2018.
    
    def var dtstart as date no-undo.
    def var iquarter as int no-undo.
    
    assign 
       iquarter = truncate( ( month( i_dt ) - 1 ) / 3, 0 ) + 1   .
       dtstart = date( ( iquarter - 1 ) * 3 + 1, 1, year( i_dt ) )
       .
    
    message
       iquarter skip 
       iso-date( dtstart ) skip
       iso-date( add-interval( dtstart, 3, "months" ) - 1 )
    view-as alert-box.   
       
    
  • Thanks Stefan, that's it !

  • Hi Unfortunately this doesn't seem to produce the correct results for the whole year.

    for example if i enter 31/12/17  (dd-mm-yyyy) it gives qtr 3 2017-07-01 - 2017-09-30

  • Are you using the code from this forum or from the initial e-mail? My unedited post had the modulo and truncate wires crossed. If I use the above and use date 12/31/17 (mdy) then I get:

    ---------------------------

    Message

    ---------------------------

    4

    2017-10-01

    2017-12-31

    ---------------------------

    OK  

    ---------------------------

    You can view this here: abldojo.services.progress.com:443/

  • Hi Stefan.  I have it working, For some reason the iso-date doesn't work in our character abl .  But i've got it working with standard date.  Thanks for your help.

  • For a ADD-INTERVAL() option, you can also use "days" as a unit (in addition to years', ‘months', ‘weeks', ‘days', 'hours', ‘minutes', ‘seconds' or ‘milliseconds'. These values are case insensitive and may be singular per the doc/help).