Formula for elapsed time + grand total

[Guest]

I have 2,300 entries of time. Column A is beginning time (23:30:48) and
Column B is ending time (01:18:24). I need to calculated elapsed time in
Column C and then add the sum of Column C to determine Average elapsed time
of the 2,300 entries. I have columns A B & C formated [h]:mm:ss. Problem is
fine until start before midnight and end after, the formula doesn't work.
Also, adding down column C for a grand total, doesn't work. Appears to not
want to go past 24.

 

[Guest]

use:

=Mod(B1-A1,1) to get value in column C

Format your Total cell as [hh]:mm:ss

 

[Sandy Mann]

Use:

=MOD(B1-A1,1)

or:

=B2-A2+(B2<A2)

--
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

 

[Guest]

The formula for Column C works out fine, but the Grand Total column isn't
giving the correct answer. The total hours should be about 24+ hours, not
the answer of 1128:35:20. Can you assist? Thank you!

Col A Col B Col C
Begin End Elapsed
18:21:52 19:24:33 1:02:41
22:16:08 06:36:37 8:20:29
19:56:01 00:44:31 4:48:30
11:49:14 17:13:53 5:24:39
12:01:04 13:08:31 1:07:27
13:47:44 16:30:08 2:42:24
15:33:57 16:33:39 0:59:42
18:12:49 18:22:17 0:09:28

Total Elapsed 1128:35:20

use:

=Mod(B1-A1,1) to get value in column C

Format your Total cell as [hh]:mm:ss

I have 2,300 entries of time. Column A is beginning time (23:30:48) and
Column B is ending time (01:18:24). I need to calculated elapsed time in
Column C and then add the sum of Column C to determine Average elapsed time
of the 2,300 entries. I have columns A B & C formated [h]:mm:ss. Problem is
fine until start before midnight and end after, the formula doesn't work.
Also, adding down column C for a grand total, doesn't work. Appears to not
want to go past 24.

 

[Guest]

The formula for Column C works out fine, but the Grand Total column isn't
giving the correct answer. The total hours should be about 24+ hours, not
the answer of 1128:35:20. Can you assist? Thank you!

Col A Col B Col C
Begin End Elapsed
18:21:52 19:24:33 1:02:41
22:16:08 06:36:37 8:20:29
19:56:01 00:44:31 4:48:30
11:49:14 17:13:53 5:24:39
12:01:04 13:08:31 1:07:27
13:47:44 16:30:08 2:42:24
15:33:57 16:33:39 0:59:42
18:12:49 18:22:17 0:09:28

Total Elapsed 1128:35:20

Use:

=MOD(B1-A1,1)

or:

=B2-A2+(B2<A2)

--
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

I have 2,300 entries of time. Column A is beginning time (23:30:48) and
Column B is ending time (01:18:24). I need to calculated elapsed time in
Column C and then add the sum of Column C to determine Average elapsed
time
of the 2,300 entries. I have columns A B & C formated [h]:mm:ss. Problem
is
fine until start before midnight and end after, the formula doesn't work.
Also, adding down column C for a grand total, doesn't work. Appears to
not
want to go past 24.

 

[David Biddulph]

You are in error by exactly 46 whole days, so my guess is that some of your
input data has date information which you are not seeing (and which you may
not be intending to use), together with the times. Format each of the cells
in column C as [h]:mm:ss so that you can get a clue where it is going wrong.
I assume that you didn't take Toppers advice to use =Mod(B1-A1,1) , as that
would have thrown away the extra days that are troubling you.

To see exactly what is in your input data, temporarily reformat columns A
and B using a custom format such as dd mmm yyyy hh:mm:ss
--
David Biddulph

The formula for Column C works out fine, but the Grand Total column isn't
giving the correct answer. The total hours should be about 24+ hours, not
the answer of 1128:35:20. Can you assist? Thank you!

Col A Col B Col C
Begin End Elapsed
18:21:52 19:24:33 1:02:41
22:16:08 06:36:37 8:20:29
19:56:01 00:44:31 4:48:30
11:49:14 17:13:53 5:24:39
12:01:04 13:08:31 1:07:27
13:47:44 16:30:08 2:42:24
15:33:57 16:33:39 0:59:42
18:12:49 18:22:17 0:09:28

Total Elapsed 1128:35:20

use:

=Mod(B1-A1,1) to get value in column C

Format your Total cell as [hh]:mm:ss

I have 2,300 entries of time. Column A is beginning time (23:30:48)
and
Column B is ending time (01:18:24). I need to calculated elapsed time
in
Column C and then add the sum of Column C to determine Average elapsed
time
of the 2,300 entries. I have columns A B & C formated [h]:mm:ss.
Problem is
fine until start before midnight and end after, the formula doesn't
work.
Also, adding down column C for a grand total, doesn't work. Appears to
not
want to go past 24.

 

[Guest]

My sum comes to 24:35:20 ... how do you calculate it?

The formula for Column C works out fine, but the Grand Total column isn't
giving the correct answer. The total hours should be about 24+ hours, not
the answer of 1128:35:20. Can you assist? Thank you!

Col A Col B Col C
Begin End Elapsed
18:21:52 19:24:33 1:02:41
22:16:08 06:36:37 8:20:29
19:56:01 00:44:31 4:48:30
11:49:14 17:13:53 5:24:39
12:01:04 13:08:31 1:07:27
13:47:44 16:30:08 2:42:24
15:33:57 16:33:39 0:59:42
18:12:49 18:22:17 0:09:28

Total Elapsed 1128:35:20

use:

=Mod(B1-A1,1) to get value in column C

Format your Total cell as [hh]:mm:ss

I have 2,300 entries of time. Column A is beginning time (23:30:48) and
Column B is ending time (01:18:24). I need to calculated elapsed time in
Column C and then add the sum of Column C to determine Average elapsed time
of the 2,300 entries. I have columns A B & C formated [h]:mm:ss. Problem is
fine until start before midnight and end after, the formula doesn't work.
Also, adding down column C for a grand total, doesn't work. Appears to not
want to go past 24.

 

[Guest]

I redid all the cells formats and formulas to Toppers advice and it worked.
Thank you very much!

You are in error by exactly 46 whole days, so my guess is that some of your
input data has date information which you are not seeing (and which you may
not be intending to use), together with the times. Format each of the cells
in column C as [h]:mm:ss so that you can get a clue where it is going wrong.
I assume that you didn't take Toppers advice to use =Mod(B1-A1,1) , as that
would have thrown away the extra days that are troubling you.

To see exactly what is in your input data, temporarily reformat columns A
and B using a custom format such as dd mmm yyyy hh:mm:ss
--
David Biddulph

The formula for Column C works out fine, but the Grand Total column isn't
giving the correct answer. The total hours should be about 24+ hours, not
the answer of 1128:35:20. Can you assist? Thank you!

Col A Col B Col C
Begin End Elapsed
18:21:52 19:24:33 1:02:41
22:16:08 06:36:37 8:20:29
19:56:01 00:44:31 4:48:30
11:49:14 17:13:53 5:24:39
12:01:04 13:08:31 1:07:27
13:47:44 16:30:08 2:42:24
15:33:57 16:33:39 0:59:42
18:12:49 18:22:17 0:09:28

Total Elapsed 1128:35:20

use:

=Mod(B1-A1,1) to get value in column C

Format your Total cell as [hh]:mm:ss

:

I have 2,300 entries of time. Column A is beginning time (23:30:48)
and
Column B is ending time (01:18:24). I need to calculated elapsed time
in
Column C and then add the sum of Column C to determine Average elapsed
time
of the 2,300 entries. I have columns A B & C formated [h]:mm:ss.
Problem is
fine until start before midnight and end after, the formula doesn't
work.
Also, adding down column C for a grand total, doesn't work. Appears to
not
want to go past 24.

 

[Guest]

I had some cells that weren't formatted as you first said and once I changed
them, it worked. Thank you very much for your help!

My sum comes to 24:35:20 ... how do you calculate it?

The formula for Column C works out fine, but the Grand Total column isn't
giving the correct answer. The total hours should be about 24+ hours, not
the answer of 1128:35:20. Can you assist? Thank you!

Col A Col B Col C
Begin End Elapsed
18:21:52 19:24:33 1:02:41
22:16:08 06:36:37 8:20:29
19:56:01 00:44:31 4:48:30
11:49:14 17:13:53 5:24:39
12:01:04 13:08:31 1:07:27
13:47:44 16:30:08 2:42:24
15:33:57 16:33:39 0:59:42
18:12:49 18:22:17 0:09:28

Total Elapsed 1128:35:20

use:

=Mod(B1-A1,1) to get value in column C

Format your Total cell as [hh]:mm:ss

:

I have 2,300 entries of time. Column A is beginning time (23:30:48) and
Column B is ending time (01:18:24). I need to calculated elapsed time in
Column C and then add the sum of Column C to determine Average elapsed time
of the 2,300 entries. I have columns A B & C formated [h]:mm:ss. Problem is
fine until start before midnight and end after, the formula doesn't work.
Also, adding down column C for a grand total, doesn't work. Appears to not
want to go past 24.