EXCEL FORMULA CHECKING AND HELP

Hello. I came on ee last year with this question and had a part work around which was good but has a few glitches. I'm attaching a small version of the file I'm working on.

I need help with the following:
Checking the formulas in the green columns. These are calculations of the Time In and Time Out columns to show the duration in FF and SS  of each shotEx-Exchange-Formula-Help.xls. I think they are correct but I'd love someone to check these to ensure they are ok. I had a fair few errors last time I had to go in and manually adjust which takes hours.

I'd love it if someone is able to expand this formula to 300 lines depth. I did have a few lines sent last time but when I transfered into another file with my text and timecodes things got tricky and messy. I suspect it would be easier and less of an error magnet if I can transfer text into a file with the calculations already on it rather than the other way around!

It was suggested I have a frame value check column to pick up any errors. There were a lot of errors last time I had to go in and manually do so I'm wondering if anyone can check that columns formula too. Essentially I need an airtight (if possible!) way to calculate Time Code IN and Time Code out to get the duration in seconds and frames of each line/shot length. I have six documents each about 300 lines long hence hoping someone can send me a document of this lenth with the formulas copied down it so I can just cut and paste in my text.

I am working with 25 frames per second. hh:mm:ff:ss

If the return document is too big it could go into my hightail link which I could send...

Thank in advance, this is headache making! I'm certainly no expert.

Tracey.
savagetraceyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barry houdiniCommented:
Hello Tracey,

Those results don't look correct to me, I'm assuming that for the first example - row 3 - you want to see 00:00:07 and 11, and for next row it should be 0:00:04 and 20

If that's right try this formula for E3

=IF(A3="","",C3-A3-(D3<B3)/86400)

and for F3

=IF(A3="","",MOD(D3-B3,25))

Those should work consistently for any values

I can't post an example right now ( I'm on the train!) but I can attach something later.

regards, barry
0
savagetraceyAuthor Commented:
Oh great Barry. Yes those are the results I'm after so I'll try these... Trace
0
savagetraceyAuthor Commented:
Hi Barry. I've tried pasting into another sheet I'm working on and just changed the row values i.e. C3 to C5 as that one starts on row 5 rather than 3. Should that make a difference?. The FF duration column calculation is working but the SS one doesn't seem to be. Unless I've copied across incorrectly.

Can I also check, if there is one calc in one cell I can click on the corner of that cell and drag down for a correct repetition of the formula down the page or do I need more than one cell with formulas i.e. 3 to copy and drag down a formula? It should save you sending a document if I can successfully copy and drag a formula down I should think.

Thanks in advance Barry!

Tracey
0
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

barry houdiniCommented:
Hello Tracey,

Yes, changing the start row shouldn't be a problem....and then both formulas can be filled or copied down the column. If you have continuous data in an immediately adjacent column then you can actually just double-click on the "fill-handle" (the black + on the bottom right of the cell) and the formula will automatically fill down as far as you have continuous data.

You say the "SS" version isn't working - can you give an example? You need to format that column as a time value

regards, barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
savagetraceyAuthor Commented:
That's it! Time format did the trick. Thanks SO much legend.

Tracey
0
savagetraceyAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for savagetracey's comment #a40699467

for the following reason:

Simple solution. Quick response. Wonderful.
0
savagetraceyAuthor Commented:
Simple solution. Quick response. Wonderful.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.