Another Excel poser!

**markyc** · 16th January 2008, 10:29 AM

Er the question that is, not me!

I need to find the running total when timing individual scenes from a script.
How do I add cells containing time values?
E.G. 1.30 + 1.31 = 3.01, not 2.61 as in normal addition? If you've ever tried to add minutes and seconds you'll know how it does your head in

**WhiteD3** · 16th January 2008, 10:39 AM

Originally Posted by markyc

Er the question that is, not me!

I need to find the running total when timing individual scenes from a script.
How do I add cells containing time values?
E.G. 1.30 + 1.31 = 3.01, not 2.61 as in normal addition? If you've ever tried to add minutes and seconds you'll know how it does your head in

Enter them as metric minutes.

ie
30 min = 0.5
15 min = 0.25

**markyc** · 16th January 2008, 10:53 AM

Trouble is, timings are rarely 15, 30, 45s etc.
What's 41s or 1min 02s in metric?
I need to enter values straight from a stopwatch to Excel and then add up to fifty values without popping a vein!

**waynep** · 16th January 2008, 10:53 AM

Fairly easy :If you select the whole column then hit :
Format
Cells
Number Tab
Custom
Select h:mm for the whole column ( or h:mm:ss if you want seconds )

Then enter your hours:mins:sec in each line and sum at the bottom
e.g enter 1:30:00 on line one and 1:30:00 on line 2
If you sum on line three should be 3:00:00

Is that what you were wanting ?

**awabbit6** · 16th January 2008, 10:55 AM

Format the cells as time

Select the cells to be added and the one containing the sum. Right click and select 'Format'. In 'Category' select 'Custom' then in 'Type' scroll down and find [h]:mm:ss as the format. Click OK to apply the format.
Excel now should add as you require

.

**dmdigital** · 16th January 2008, 11:03 AM

There's a few ways you can do this. I assume the time codes are mm.ss

In this case I would use the following formula:
=INT(A1)+INT(B1)+MOD((A1+B1-(INT(A1)+INT(B1)))*100,60)/100+INT((A1+B1-(INT(A1)+INT(B1)))*100/60)

Where A1 = 1.30 and B1=1.31

**markyc** · 16th January 2008, 01:50 PM

dm, you're on the money!
I substituted B1 for A2 to stack my entries vertically; would I simply change again to A50 (say) for 50 entries? And can I save your formula without making the above change and copy/paste every time?

Thanks!

**dmdigital** · 17th January 2008, 05:25 PM

Hi Mark,
Sorry about the delay in posting a reply. I was on a day off yesterday, but at work today.

If you want to sum a column A1, A2,..., A50 I would do the following. In column B:
B1: =A1
B2: =INT(B1)+INT(B2)+MOD((B1+B2-(INT(B1)+INT(B2)))*100,60)/100+INT((B1+B2-(INT(B1)+INT(B2)))*100/60)
B3: =INT(B2)+INT(B3)+MOD((B2+B3-(INT(B2)+INT(B3)))*100,60)/100+INT((B2+B3-(INT(B2)+INT(B3)))*100/60)

etc... You can drag the formula down to B50

Bit of an explanation:
The minutes are the integer portion so - INT(B1) - we take the minutes and we add them together.
The Seconds are the decimal fraction. So we remove the minutes and multiply by 100 to get them as integers. Next we take the modulo 60 of this number to return the number of seconds more than a whole minute (the modulo of a number is the remainder left over that is not divisible by the number). We then divide this by 100 to turn it back into a decimal fraction
The third thing we do is take the integer portion of the seconds divided by 60. This is the number of whole minutes in the seconds.
Lastly we add it all together and get mmm.ss

Another way to do it would have been to convert everything to seconds, add the seconds so B1 to B50 would be:
B1: =60*INT(A1)+100*(A1-INT(A1))
.
.
.
B50: =60*INT(A50)+100*(A50-INT(A50))
and lastly:
B51: =INT(SUM(B1:B50)/60)+MOD(SUM(B1:B50),60)/100
The trick is to treat the decimal portion as Base60

You can't use the time format as Excel thinks time is days.seconds since 1/1/1900.

Thread: Another Excel poser!

Thread Tools

Another Excel poser!

Bookmarks

Bookmarks

Posting Permissions