Summarize durations by month?

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Answered
Hi everyone:

I'm looking to do some duration reporting and I'm not sure that it's possible. I have a formula-duration field that calculates the difference (in days) between two dates. That part works great. But the folks I am working with would like that information to be summarized by month - so they could get a count of things that took less than one month, less than two months, longer than two months, etc. 

Is something like this possible?

And, for extra credit - there are items that have no duration, marked "LTF" in another field. They would also like those totaled. So the results would be something like:

>1 month      12
>2 months     34
LTF                85

Doable? Partly doable? Let me know what you think, and thanks so much for any help you can provide. 

Suzanne
Photo of Suzanne Davidson

Suzanne Davidson

  • 170 Points 100 badge 2x thumb

Posted 4 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,306 Points 50k badge 2x thumb
Sounds like you just need a formula field to calculate the Duration Bucketing. Then just make a summary report on that field.

IF(
[Other field]="LTF", "LTF",
[Project Duration] < Days(30), "< 30 days",
[Project Duration] < Days(60), "30 to 60 days",
[Project Duration] >= Days(60), "60 days +")

You will need to fiddle with adding extra spaces in the text result to get them to sort correctly.
(Edited)
Photo of Suzanne Davidson

Suzanne Davidson

  • 170 Points 100 badge 2x thumb
Thanks! That seems like it would work - but it's ok to mix text and duration like that? It was yelling at me when I tried before. 
Photo of Suzanne Davidson

Suzanne Davidson

  • 170 Points 100 badge 2x thumb
Update: Just tried it and this works great. I had to use Formula:Text type. Thank you!!