Google Spreadsheet calculating time and showing difference

Question:

Hi Folks,

I am making a template to track the runtimes of Scenes and Acts of an hour long TV show. Acts are what we call all the scenes between commercials. Hour-long shows have 5 acts, half an hour shows have three acts...

While we're editing, we need to know how far we're off by...act by act and the overall time. Maybe we need to move a scene into another act...maybe we need to cut a scene. It's a very back-and-forth process! I need to keep track of a number of different shows, so I'm looking for a formula that can tell me where we're at.

Here's a screenshot of my spreadsheet, that I've been working on:

- In the blue cells, I enter the running times of each scene as we edit them. (THIS SHOULD BE IN MINUTES AND SECONDS...I MAY HAVE THE FORMAT OFF ON THIS, FOR EXAMPLE, "Scene 3" is 3 minutes and 38 seconds, not 3:38 am)
- A simple SUM formula totals up the times for me in the green cell, which gives me the ACTUAL RT (Running Time) of that respective Act.

- I want a formula in the yellow cell which will compare that SUM (in the green cell) with the values in the pink cells.

- If the formula finds that the runtime falls between that range, I would like the cell to say "OK!", and turn green.
- If the formula finds that the runtime falls below that range, I would like the cell to say "UNDER by (difference)". For example, cell H15 should say "UNDER BY :39", and be colored red.
- If the formula finds that the runtime falls above that range, I would like the cell to say "OVER by (difference)".  For example, cell E15 should say "OVER BY :57", and be colored red.


Finally, I'd like and overall sum of all the green cells to tell me how long the show is, and how much more we need to edit out. Our show needs to be 43 minutes and 30 seconds, so I'd like to know how much we're off by at a glance.

Thanks very much to anyone that lends help!

Solution:

First of all, I would like to give you suggestion to use time format as HH:MM:SS, for example 2:12:33 where it means 2 hours 12 mins and 33 seconds. And even if you have just 45 seconds then use 0:00:45.
It would be easy for you to have calculation on it in your spreadsheet.

So now, I have converted your spreadsheet in HH:MM:SS format. Have a look at screenshot below:

Now to get the sum in Cell B11, put the following formula in it:
=SUM(B4:B9)

And now to compare it will Cell B12 and B13, put the following formula in Cell B15:
=if(and(B11>=B12,B11<=B13);"OK!";if(B11<B12;"Under By:";"Over By:"))

the above formula will show you whether difference is "OK!" or "Under By" or "Over By"

And put the following formula in Cell B16:
=if(and(B11>=B12,B11<=B13);"";if(B11<B12;(B12-B11);(B11-B13)))

the above formula will show you the difference.

Now, to change the color as per your requirement, click on the Cell B15 and then select "Format" >> "Conditional formatting...". Have a look at the screenshot below:

Now in Conditional formatting window, put "OK!" in text box near "Text contains" and then change background color to green and then click on "Add another rule" and put "Over By:" and select background color as red, again add another rule and put"Under By:" and then put background color as red, and then click on "Save rules". Have a look at the screenshots below:

Screenshot of conditional formatting window:

So now you are done with formula and formatting with Cell B15 and B16. Now you can simply select these both cells and copy (ctrl C) and paste on Cell E15 and E16 then again paste it on Cell H15 and H16 and so on...
So this will quickly paste the formula and formatting on these cells..

Have a look at the final screenshot of the revised spreadsheet:

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,

Kishan.

iGoogleDrive