Yeah, that all makes complete sense. I was over thinking it and you're right, at no point does the 0.02 dropped actually make a difference. Especially with the doc I'm trying to create where frames will probably get rounded down to the nearest second anyway
Thanks for give this some thought for me though!
Do you remember if you ever hit any issues with this macro?
I'm using your worksheet as a base with the macro active I'm creating a sheet with columns for the following
(A) Video Source
(B) Timecode in
(C) Timecode out
(E) Duration as frames
I'm using the macro to work out the difference between B and C =Num2Time(Time2Num(G2)-Time2Num(F2)) I'm then converting that time duration to frames in column E.
The on a seperate tab I'm using a SUMIF to collect the values from column E based on the video source in column A, before converting those frame totals back to time. Basically it's intended to add up all of the sources across the piece of content to give me a total from each supplier.
It all works which is amazing.
But then if I drag the formulas down the columns or add any, suddenly it all stops working and I get the frustrating #VALUE!
I'm looking at the formulas in each cell and they're all correct. If I rebuild it all from scratch it starts working again. But if anything is added or dragged again, it stops working. I can't for the life of me see what's changing to throw it out.
I've not really used macros before. Is there anything fundamental I'm doing which would cause this do you think?