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?
 
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
 
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.