Reporting a number of days the status of a job was running between 2 custom dates.

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • Answered
I have fields for the following
Job ID
Status
Start
Stop

At any time, I can get a request to count and report on my job days.    My definition of a job day is a job that is running on that day is 1.    

However, if I need to run this report from the 8th of the previous month through the 8th of this month, I don't want the days the job was still running before or after this date to be counted in the total number of job days.     

How would I get this to track accurately and be able to pull the report easily.   
Photo of Jabrey Howell

Jabrey Howell

  • 184 Points 100 badge 2x thumb

Posted 1 month ago

  • 0
  • 1
Photo of Forrest Parker

Forrest Parker

  • 598 Points 500 badge 2x thumb
I can think of two options depending on the requirements.

1) If you need to have complete control over the date range every time you run the report, then set the date filters to <ask the user>


2) If you always need to count the job days during the previous "month" from the current day then create a Field that calculates the date that is a month before the current day and use that as your "is on or after" filter.  The "is on or before" filter would be "current day".  

The one month ago formula would be a formula - date field and would look like the following:

AdjustMonth([Date],-1)

Photo of Jabrey Howell

Jabrey Howell

  • 184 Points 100 badge 2x thumb
I like that possibility. 
  
During the wait for assistance, I created 2 new Formula Date fields.   Reporting Start Date and Reporting Stop Date.   

The Reporting Start Date field looks at if the actual Start Date field is >30 days ago, it will use 30 days ago as the Reporting Start Date. 
         My formula   
  Max([Start],(Today()-Days(30)))

The Reporting Stop Date will look at the Actual End date, and if it is populated it will use it, otherwise it will use Today. 
         My formula
Min([End],Today()) 

Then, I set up to filter the jobs to those that had a Start or End date that is within the last 30 days.    

So far, I think it is working correctly.