formula to calculate the start times for various activities

Answers

  • I have a basic event schedule for broadcast TV. I know what time we go to air, I'm trying to find a formula to use to calculate the start times for various activities that lead up to us going on the air. Easy to do in Excel but I'm having trouble in Smartsheet. My schedule is attached.

    Any ideas? I'm pulling information from a Master Schedule using a IFERROR / INDEX MATCH formula to populate the time and event name, I want to auto-calculate the times in the Central Time Zone column with a formula calculating from the ON-AIR Column....


  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭

    Hi @DMCARBRAY -- are you still looking for help with this?

  • Yes, still hoping to be able to have a formula in the column that will calculate the start time of the activities based on the "On-Air" time. Do you have any ideas?

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 12/31/23

    Hi @DMCARBRAY 

    I assumed you want to calculate the start time of a series of activities backward from the "On Air" time based on each activity's duration.

    Excel's Time Calculation

    With Excel, if you have two values in a time frame, you can add or subtract those values.

    Using time is not what Smartsheet is good at. We need the Hour and Minutes parts with the TEXT functions, such as the FIND, Left, and RIGHT functions.


    Luckily, Smartsheet introduced the TIME function, which can convert AM/PM to 24 Hour Time or vice versa.

    Converting AM/PM format to 24-hour time makes getting the Hour and Minutes part easy as you can use the:

    • Hour = RITGHT( Time Text, 2)
    • Minutes = LEFT(Time Text, 2)

    (You do not find the ":" position to get Hour & Minutes part or add 12 hours if PM.)

    So much for the introduction, let's solve your problem.

    Duration

    The first five columns in the demo sheet below caliculate each activity's duration based on your provided Time data. For example, 120 of "MTVG EIC Call time" means that the activity needs to start 120 minutes before the next activity, "Crew Call: TD EVS Audio Video Graphics Cameras Utility". With your data, some activity has a negative value. For example, "Visitors Batting Practice - Cameras 2" has -70, meaning the activity must start 70 minutes after the next event, "5:00 PM Cubs Live".

    Start Time

    The "On Air" time is given in the Sheet Summary fields. 8:00 PM in this example.

    Minutes helper column

    For ease of calculation, I added the Minutes column, which has the following formula;

    • [Minutes]=H# * 60 + M# - (SUMIFS(Duration:Duration, Row:Row, >=Row@row))

    The H# * 60 + M# part converts the On Air time to minutes value. For example, 8:00 PM = 20*60+00 = 1600 (H# is the On Air Hour in 24-hour format.)

    The SUMIFS(Duration: Duration, Row:Row, >=Row@row) part is the sum of duration up to a specific activity. For example, up to the "5:00 PM Cubs Live" activity, the SUMIF will give a sum of 0+15+105 = 120.

    Start H and Start M

    Then convert the Minutes value to Start H and Start M.

    • [Start H] =INT(Minutes@row / 60)
    • [Start M] =RIGHT("0" + (Minutes@row - 60 * [Start H]@row), 2)

    Start Time

    Finally, create AM/PM format Start Time with this formula;

    • [Start Time]=TIME([Start H]@row + ":" + [Start M]@row, 0)


  • @jmyzk_cloudsmart_jp

    Thank you for the solution you provided above. Really impressive and I appreciate you putting such effort into it.

    I ended up being able to accomplish my goal using the new Time Function in Smartsheet and this formula to simply deduct a number of minutes from the "On-Air" time and calculate the start time of events leading up to "On-Air".

    Text/Number=TIME([Time Reference]@row) - 2703:30 AM Subtract 270 minutes from a time object

    Thanks again,

    Dave

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    @DMCARBRAY 

    Glad you found a solution!😀

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!