Formula

nkosikhona.msani89271

Hi There,

I have a list of requests that have dates that were created, so I want to run a report for all the requests that have been opened in the past 6 months from the date I am presenting my report on.

Is there a formula I can use to do this calculation as I want to add this as part of my dashboard?

Kind Regards

Nkosi

Andrée Starå

Hi Nkosi,

You’ll configure that in the Report Builder in the When? Section. Select the is in the last (day(s) option and select the number of days to look for.

Make sense?

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

nkosikhona.msani89271

I want to show my changes on the Bar Graph as per status (In Progress or Completed) so having the report won't help as I need to present every months and I need my presentation to have graphs not a list while I think the report will give me a list.

nkosikhona.msani89271

I have tried using this formula but it not giving me the right answer as it gives me 0: =COUNTIF({OLD ICR Tracker - 2006 Range 1}; @cell >= IFERROR(MONTH(TODAY()) - 6; 1))

Is there someone who can assist.

nkosikhona.msani89271

That the formula I am using to calculate the number of total changes submitted in the past 6 months but I need it to auto-update itself, I don't want to update this whenever I need to do the presentation.

Paul Newcome

You would need to put a Copy Row Automation in place to copy the rows over to another sheet when the change is made. Insert a Created (date) type column on the target sheet, and you will be able to reference that for your metrics to track what was changed and when.

nkosikhona.msani89271

@Paul Newcome , I don't want to copy or move to another sheet.

Just to try and and give sum clarity on what I want to achieve here is that I need to report on a 6 months rolling metrics calculation system. The data involved will be from the one column which is: Date Created column, so what I want to do is to calculate the SUM of changes created/submitted in the past 6 months without always updating the formula.

I hope this gives clarity on what I want to achieve.

Paul Newcome

My apologies. I thought you meant you were trying to track what rows were changed in the past 6 months. Try something like this...

=COUNTIF({OLD ICR Tracker - 2006 Range 1}; @cell >= DATE(YEAR(TODAY(-180)), MONTH(TODAY(-180)), 1))

nkosikhona.msani89271

@Paul Newcome I tried it, it give me an error as per below:

nkosikhona.msani89271

@Paul Nelson, It Final Worked, I had to change (,) to (;) and it worked perfectly

Thank you very much

Paul Newcome

My apologies. Your region uses semicolons instead of commas.

=COUNTIF({OLD ICR Tracker - 2006 Range 1}; @cell >= DATE(YEAR(TODAY(-180)); MONTH(TODAY(-180)); 1))

Paul Newcome

Looks like we were typing at the same time. Glad we were able to help you get it working.

Please don't forget to mark the most appropriate response(s) as "helpful" so that others searching for a similar solution can know that one may be found here.

Andrée Starå

@nkosikhona.msani89271

I saw that Paul answered already!

Let me know if I can help with anything else!

Best, 

Andrée