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
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!