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

D Paige

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

Frank S.

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.

D Paige

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.

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/dashboards/C4fJW8mRF6qFpGphwvw2HW6G28fx5W9xPm9WhFM1