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 autocalculate the times in the Central Time Zone column with a formula calculating from the ONAIR Column....

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 "OnAir" time. Do you have any ideas?

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 24hour 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 24hour 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 "OnAir" time and calculate the start time of events leading up to "OnAir".
Text/Number=TIME([Time Reference]@row)  2703:30 AM Subtract 270 minutes from a time object
Thanks again,
Dave

Glad you found a solution!😀
Help Article Resources
Categories
Check out the Formula Handbook template!