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

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 ?

• 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 )

```
All Replies
• dtFirstOfMonth = dtDate - (day(dtDate) - 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 )

```
• 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).