Calculating current quarter start and end

erugalatha

Hi,

I have a dropdown on a form that allows the user to select Monthly,Quarterly etc. I need to calculate a period depending on what is selected.

e.g. if monthly is selected then I got to calculate the period in the format:
DD MM YY - DD MMM YY
where the first DD MMM YY is the first day of the current month and the second DD MMM YY is the last day of the current month.

if quarterly is selected then I need to calculate the current 3 month quarterly period in the above format.

Anyone know a way to do this?

David_Singleton

CALCDATE -

<-CQ> first day of quarter
<CQ> last day of quarter

(or at least from memory it is, its something like that, you may need to play a bit.)

Waldo

Correct, David.

This is from Help:

CALCDATE (Date)
Calculates a new date based on a date expression and a reference date.

NewDate := CALCDATE(DateExpression [, Date])
NewDate

Data type: date

The date the system computes from the reference date and the date expression.

DateExpression

Data type: text, code or dateformula

The date expression can be any length. The system interprets the string from left to right, one subexpression at a time. The following rules describe the valid syntax of date expressions:

DateExpression =
<SubExpression>


<SubExpression>
[<Sign>] <Term>



<Sign>
+ | -


<Term>
(see below)


This is the syntax of <Term>:

<Term> =
<Number><Unit> | <Unit><Number> | <Prefix><Unit>

<Number>
Positive integer

<Unit>
D | WD | W | M | Q | Y

(D=day, WD=Weekday, W=Week, M=Month,

Q=Quarter, Y=Year)

<Prefix>
C(C=Current)

omyvadiya

Hi Guys,

I have a date and i need to know in which quarter it falls. How can this be achieved.

My Quarters are:
Q1 = April to June
Q2 =July to Sept
Q3 =Oct to Dec
Q4 = Jan to Mar

For Eg: The Ref date is 30-04-15, which is a date field of a X table.

I tried using Calcdate:
datQuarter := CALCDATE('<CQ>',RefDate);
MESSAGE('datQuarter - %1',datQuarter);

Variable :
datQuarter date (datatype)
RefDate date (datatype)

But the error i get while executing is

---

Microsoft Dynamics NAV Classic

---

You cannot base a date calculation on an undefined date.

Date: 0D
Formula: CQ

---

OK

---

omyvadiya

Hi Guys,

I have a date and i need to know in which quarter it falls. How can this be achieved.

My Quarters are:
Q1 = April to June
Q2 =July to Sept
Q3 =Oct to Dec
Q4 = Jan to Mar

For Eg: The Ref date is 30-04-15, which is a date field of a X table.

I tried using Calcdate:
datQuarter := CALCDATE('<CQ>',RefDate);
MESSAGE('datQuarter - %1',datQuarter);

Variable :
datQuarter date (datatype)
RefDate date (datatype)

But the error i get while executing is

---

Microsoft Dynamics NAV Classic

---

You cannot base a date calculation on an undefined date.

Date: 0D
Formula: CQ

---

OK

---

Jan_Veenendaal

Hi,

The error you get is because RefDate probably has no value.

If is HAS a value, then you can use this formula to calculate your quarter:

q := (((( DATE2DMY(RefDate,2)-1) div 3) + 3) mod 4) + 1;

A little explanation:
DATE2DMY(RefDate,2) gives you the month number. Subtract 1 to get it zero-based ( numbered from 0..11 instead of 1..12 )
Then div 3 gives you a 'normal' quarter number 0, 1, 2 or 3. In normal cases you should just add 1 and be finished.

Because your first quarter is starting in April, you would like this list to be 3, 0, 1, 2
You can achieve that by using modulo 4 calculation: add 3 and then calculate mod 4
As a last step you need to translate 3, 0, 1, 2 to 4, 1, 2, 3 --> so just add 1.