Formula to number months within a quarter?
Within Q1, January is the first month (1), February the second (2), March the (3rd). Then Q2 starts, and April is the first (1), May is the second (2), and so on.
Is there a good way to set something like this up?
I ask because I want to build an IF AND statement, in which IF a value exists within a cell AND that cell is #2 within that quarter, a cell will be populated.
Best Answer
-
Use QUARTERTODATE(1) function
This increments and resets, at the end of each quarter
I hope that helps
David
4
Answers
-
Hi,
I think the function QUARTERTODATE will help here;
In your time settings module, have a period count line item, value of "1", and then have your QUARTERTODATE line item reference this line item.
Best,
Callum
0 -
I am not sure using QUARTERTODATE as described by others gives you the full explanation.
If you are looking to have a query which directs the outcome to a specific action such as the 2nd period of each quarter results in a certain outcome etc. then you need to create a custom mapping against time.quarters which will direct the query to the desired action.
If this is so, create a numbered list corresponding to each desired unique action.
Then map this list to each quarter in a time settings systems module.
In your target create your IF THEN statement using this systems module to map to the desired action.
Example - IF targetcell.value[LOOKUP: mapping.unique action] then x else next query...etc
* by Action I refer to any desired outcome which has been mapped in the time setting systems module.
Hope this helps.
Chris
0 -
Using QUARTERTODATE is simply an automated way of populating the mapping and would maintain the mapping as time frames are updated. Otherwise the mapping would need to be manually updated each time the model calendar was changed.
Perhaps my contribution is more of an explanation as to why this would be a good approach!!0 -
If you do need to check for the 1st, 2nd or 3rd periods in the qtr, I would also do this in the time settings module as the calculation doesn't change in relation to other dimensions
David
1
