I need help with a formula to track time withen a date range of a certain criteria
I'm looking to track the TOTAL PROD TIME of a certain criteria (say MCBB) of SPORTSTUDIO range within a set of dates. Any help with this is GREATLY APPRECIATED!
If its needed this is the formula I use to track the individual TOTAL PROD TIME's:
=INT([SUM OF ON AIR OFF AIR TIME]@row) + ":" + IF(([SUM OF ON AIR OFF AIR TIME]@row  INT([SUM OF ON AIR OFF AIR TIME]@row)) * 60 < 10, "0") + ([SUM OF ON AIR OFF AIR TIME]@row  INT([SUM OF ON AIR OFF AIR TIME]@row)) * 60
Answers

Hello @D Paige
If you want to complete the formula above based on a date range criteria, I would suggest something like the following:
=IF(AND([Date]@row >=Date(YYYY,MM, DD), [Date]@row <= Date(YYYY,MM, DD)), INT([SUM OF ON AIR OFF AIR TIME]@row) + ":" + IF(([SUM OF ON AIR OFF AIR TIME]@row  INT([SUM OF ON AIR OFF AIR TIME]@row)) * 60 < 10, "0") + ([SUM OF ON AIR OFF AIR TIME]@row  INT([SUM OF ON AIR OFF AIR TIME]@row)) * 60, "")
You will need to put the date ranges you want in the 2 date areas.
I hope this helps.
Frank Smith, PMP
Assistant Director  IT Special Projects Mgr.
Oregon Parks & Recreation Department
If my response helps, please mark it as an accepted answer. 😎

I'm sorry Frank I don't believe I worded my question correctly. How would I add another Range being SPORTSTUDIO and the search criteria being MCBB to the date and time's formula you have listed above. Your formula is amazing, sorry for the confusion.

Hi @D Paige
Why not try a formula like this?
=SUMIFS({Time Hour}, {Criteria}, Criteria@row, {Date}, AND(@cell >= Start@row, @cell <= End@row))
Please replace {Time Hour} with {SUM OF ON AIR OFF AIR TIME} reference in your case.
I hope I am interpreting the intent of your question correctly.
For more information, please see the demo dashboard at the link below.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 351 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!