calculate elapsed time

[Tiresias]

Hi everyone,
Probably a really simple problem but I can't seem to figure it out from reading excel help files. I'm trying to create a worksheet which will calculate the running times of individual components (or episodes) on DVDs. The user will enter the end time of each component in Column A (formatted in minutes and seconds) and the worksheet will subtract the endtime just entered from the endtime in the sell above giving the running time of that component (in minutes and seconds).

Example:
The user enters the following values into column A: 23:41, 47:04, 01:11:16, 01:35:14.
The worksheet returns the following values in column B: 23:41, 23:23, 24:12, 23:58.

Also, it would be good to not have to enter colons/full stops between minutes and seconds in column A.
Example: entering 012506 and hitting enter will automatically convert to 01:25:06 in column A.

I'm using Excel 2007.
Thanks in advance for your help.

[daddylonglegs]

If you enter the data as valid times then you have to also enter the hours, i.e. 0:23:41 (otherwise Excel will interpret 23:41 as 23 hours and 41 minutes)

If you have A1 blank and enter the time data in A2 down then you can use this formula in B2 copied down

=A2-A1

If you want to enter without colons you'll need VBA to actually convert to a valid time on entry......or you can enter 12506 (you dont need any leading zeroes) and make it display like a time by formatting column A as

00\:00\:00

...but because this isn't really a time (the underlying value is still the number 12506) you need a slightly more complex formula to calculate the difference, i.e. in B2 again

=TEXT(A2,"00\:00\:00")-TEXT(A1,"00\:00\:00")

[shg]

Maybe like this:

Please Login or Register  to view this content.

[Tiresias]

Thanks for both of those solutions - even a novice like me was able to make them both work.
Cheers!