formula to calculate the start times for various activities

System

This discussion was created from comments split from: Formulas for auto creating start time / end time based on duration - Event Runsheet.

DMCARBRAY

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

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

DMCARBRAY

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

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.

https://help.smartsheet.com/function/time

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)

https://app.smartsheet.com/b/publish?EQBCT=7b3d38a449d34318864c5c0a9adc80c6

DMCARBRAY

@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

@DMCARBRAY 

Glad you found a solution!😀