# I need help with a formula to track time withen a date range of a certain criteria

✭✭
edited 03/13/23

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

• ✭✭✭✭✭✭

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.