7 Replies Latest reply on May 26, 2018 9:36 PM by Deepak Rai

# Calculating days in Quarter between 2 dates

Hi All,

I have a challenging request from my client which i am unable to get my head around and need your help.

The Process is every quarter an employee has to perform Risk Assessments to ensure that there are no issues in the RCSA which he is responsible of.

The client would like to break down the Risk Assessments in below categories.

• On Time= Done in the last week of the Quarter.
• Early= Done in the before the last week of the Quarter
• Late= Done after 10 days of the next quarter. (e.g. Days in Q1+11 days of next quarter)
• Not Assessed: Assessment due in Q1 but not assessed in current or next quarter.

The client would like to know how many are falling under which category by selecting the quarter of his choice. Below is the snapshot and attached is the sample data along with the .twbx

Please see the data set as below. The status column is whats desired and its not in the data provided here.

• ###### 1. Re: Calculating days in Quarter between 2 dates

Hi Shariq,

I think I got it. I just could not catch logic for Not Assessed, but you can modify the calculation 5 in attached.

Thanks

Deepak

If it Helps, pl mark it Helpful and CORRECT to close Thread.

• ###### 2. Re: Calculating days in Quarter between 2 dates

Deepak Rai thanks for your effort, i think its very good progress what you have done The problem that i see with this formula is that its static to only Q1 and Q2 of 2018/ The real data will have old and new data e.g. will have data from 2002 and will keep on increasing. This needs to be dynamic to handle quarter from any year, what do you think we can do to make this dynamic.

IF [Quarter]="Q1" THEN #3/31/2018#

ELSE #6/30/2018#

eND

• ###### 3. Re: Calculating days in Quarter between 2 dates

So you mean that you have full dates in raw data?? Then it is easy if we have full dates instead of q1 q2 give me that

• ###### 4. Re: Calculating days in Quarter between 2 dates

Yes, I am attaching a more appropriate data set here, Reminder: the logic above remains same and are based on Quarters

• ###### 5. Re: Calculating days in Quarter between 2 dates

Thanks

Deepak

• ###### 6. Re: Calculating days in Quarter between 2 dates

Deepak Rai yes it worked, It showed me the way to solve the problem.

Thank you

• ###### 7. Re: Calculating days in Quarter between 2 dates