HH:MM:SS

[Francis Hookham]

HH:MM:SS



A storyline has length of scene in col D and running total in col F and
times are entered in seconds.

So:



Scene 1 B1 = 5 D1 = 00:00:05
(=B1)

Scene 2 B11 = 20 D11 = 00:00:25
(=B11+D1)

Scene 3 B21 = 55 D21 = 00:01:20
(=B21+D11)

Etc



Question 1

How should the cells be formatted?

Presumably "HH:MM:SS" or "MM:SS" if only minutes and seconds needed.



Question 2

Can the seconds be entered into col B simply as a numeral "5" or must they
be entered as "00:00:05"?



I have not needed to work with time for ages and I am getting into an awful
muddle!

Please help.



Francis Hookham

 

[Bernard Liengme]

If times are entered as numbers ( 5, 20,35) in column B
Then D1 need =TIME(,,B1)
D11 needs =D1+TIME(,,B11)
Yes, format as HH:MM:SS
best wishes

 

[Tyro]

Excel maintains times as fractions of 24 hours. 1 hour is 1/24, 1 minute is
1/(24*60) and 1 second is 1/(24*60*60). If you enter a 5 you're telling
Excel 5 days not 5 seconds as in the entry 00:00:05 which is 1/(24*60*60) *
5 - i.e 0.0000578703703703704 which is 5 seconds. You can format the times
as [h]:mm:ss which will show the elapsed time even if the time exceeds 24
hours. If you enter a 5 to be 5 seconds you will have to do the math to
convert that to Excel's 5 seconds. In other words use the TIME function. If
you enter 5 in A1 to mean 5 seconds, use =TIME(0,0,A1) to get 5 seconds in
Excel's time.

Tyro

 

[Francis Hookham]

Thanks to you both, Tyro and Bernard - just what I wanted.

One further question:

In XL one can format "#,##0.00" using the hash to determin what happens if
the number is greater than 9.

Is there a way of formating the hh:mm:ss cells so that mm:ss show until more
than 59mins and 59secs - I hope that's clear.

45mins and 25 secs is displayed as 45:25 and not as 00:45:25

but 75mins and 25 secs automatically show as 01:15:25 withing the same
formatted cell.

Thanks

Francis

 

[Sandy Mann]

Question 1

Presumably "HH:MM:SS" or "MM:SS" if only minutes and seconds needed.

Yes provided that the total time will not go over 60 minutes otherwise you
will need the HH as well.

Question 2

Can the seconds be entered into col B simply as a numeral "5" or must they
be entered as "00:00:05"?

I would have a helper column - say Column C with the formula:

=IF(B2<>"",B2/86400,"")

then in Column D2:

=IF(C2<>"",C2)

In D3:

=IF(C3<>"",SUM($C$2:C3),"")

and drag down on the fill handle, then hide Column C

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk