Adding large times in excel 2003

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lester Mosley - 06 Aug 2007 23:15 GMT

I seem to have an issue when calculating department totals for staff
time in excel.

I am pulling data off the phone system. it gives a time say of
15004:44:23 and another department is 13894:34:23
I need to add these two totals up in excel but it does not like it and
returns a #value.

I have formated as [h]:mm:ss and [hh]:mm:ss as well as the time format
as hh:mm:ss and neither works. it always returns just #VALUE

It seems after about 12000 hours excel fails to see it as a "number"
to add

Is there a formula that will work that will allow me to add these
large times? or how to mke excel understand these are hours minutes
and seconds in the way i need to add "staff time"

I would appreciate it

Reply to this Message

Peo Sjoblom - 06 Aug 2007 23:50 GMT

It's because they are seen as text, you can't type in time values like that
but you can fool Excel and type them in as

=(15004/24+44/1440+23/86400)+(13894/24+34/1440+23/86400)

and format as [hh]:mm:ss and you will get a time value 28899:18:46 using
your example

However if the values are imported it might be hard, are they always in the
format of

[hh]:mm:ss

so if one value has zero seconds it will still import as

10125:25:00

If so, with one value in A1 and the other in B1

=(LEFT(A1,FIND(":",A1)-1)/24+SUBSTITUTE(A1,LEFT(A1,FIND(":",A1)),"")/60)+LEFT(B1,FIND(":",B1)-1)/24+SUBSTITUTE(B1,LEFT(B1,FIND(":",B1)),"")/60

so the easiest way if you are summing a range of these values would be to
put this formula in an adjacent cell

=LEFT(A1,FIND(":",A1)-1)/24+SUBSTITUTE(A1,LEFT(A1,FIND(":",A1)),"")/60

and either copy down or across then sum that adjacent column/row

Make sure you use a custom format [hh]:mm:ss
or else you would get decimal values

Signature

Regards,

Peo Sjoblom

>I seem to have an issue when calculating department totals for staff
> time in excel.
[quoted text clipped - 15 lines]
>
> I would appreciate it

Reply to this Message

Lester Mosley - 07 Aug 2007 00:01 GMT

> It's because they are seen as text, you can't type in time values like that
> but you can fool Excel and type them in as
>
> =(15004/24+44/1440+23/86400)+(13894/24+34/1440+23/86400)

I manually type them in so this may work.. I will have to see as they
are not imported at this time.

> Make sure you use a custom format [hh]:mm:ss
> or else you would get decimal values

for the say 15004:44:23
would i need [hhhhh]:mm:ss or is [hh]:mm:ss sufficient?

Reply to this Message

Peo Sjoblom - 07 Aug 2007 00:21 GMT

>> Make sure you use a custom format [hh]:mm:ss
>> or else you would get decimal values
>
> for the say 15004:44:23
> would i need [hhhhh]:mm:ss or is [hh]:mm:ss sufficient?

If you type them in you can use the method I provided and it's enough using

[hh]:mm:ss

to explain it, one day in Excel equals 1, meaning that one hour is 1/24
one minute is 1/24/60 which is the same as 1/1440
one second is 1/24/60/60 which is the same as 1/86400

you can also write it a little bit shorter

=15004/24+TIME(0,44,23)+13894/24+TIME(0,34,23)

or

=15004/24+"00:44:23"+13894/24+"44:23"

since the hours are really the only thing you need to do this with.

Reply to this Message

Lester Mosley - 07 Aug 2007 00:34 GMT

Thanks. I do need to have a YTD and a overall organization totals so
this should help a lot now.

I appreciate your help!

Reply to this Message

Peo Sjoblom - 07 Aug 2007 00:49 GMT

I had a typo in the last formula, it should have been

=15004/24+"00:44:23"+13894/24+"00:44:23"

Thanks for the feed back

Good luck!

Peo

> Thanks. I do need to have a YTD and a overall organization totals so
> this should help a lot now.
>
> I appreciate your help!

Reply to this Message

Peo Sjoblom - 07 Aug 2007 00:51 GMT

Blasted!

=15004/24+"00:44:23"+13894/24+"00:34:23"

Peo

>I had a typo in the last formula, it should have been
>
[quoted text clipped - 10 lines]
>>
>> I appreciate your help!

Reply to this Message

Dave Peterson - 07 Aug 2007 00:51 GMT

You may want to use separate columns for your data entry.

Then use another column to do the arithmetic.

Lester Mosley wrote:

> Thanks. I do need to have a YTD and a overall organization totals so
> this should help a lot now.
>
> I appreciate your help!

Signature

Dave Peterson

Reply to this Message

Lester Mosley - 07 Aug 2007 14:01 GMT

=LEFT(A1,FIND(":",A1)-1)/24+SUBSTITUTE(A1,LEFT(A1,FIND(":",A1)),"")/60

seems to work great when it is 15000 but when 1500 it does not. What
am i not seeing here or doing wrong?
What can i do to make this work with 15000:10:10 1500:10:10 or even
00:00:55

Reply to this Message

Lester Mosley - 07 Aug 2007 14:32 GMT

> =LEFT(A1,FIND(":",A1)-1)/24+SUBSTITUTE(A1,LEFT(A1,FIND(":",A1)),"")/60
>
> seems to work great when it is 15000 but when 1500 it does not. What
> am i not seeing here or doing wrong?
> What can i do to make this work with 15000:10:10 1500:10:10 or even
> 00:00:55

Actaully i converted everything to text - and this formula works
except when you are doing times under 1 minute
ie:: 00:00:15

anyway to correct that?

Reply to this Message

Peo Sjoblom - 07 Aug 2007 16:07 GMT

If you don't force to text and just let Excel decide you could use

=IF(ISNUMBER(A1),A1+whatever,Left(A1etc

Since all numerical time entries can just be added like

=A1+B1

so if you test if the entry is a number, then just do a regular addition,
then if not you use the text conversion

Signature

Regards,

Peo Sjoblom

>> =LEFT(A1,FIND(":",A1)-1)/24+SUBSTITUTE(A1,LEFT(A1,FIND(":",A1)),"")/60
>>
[quoted text clipped - 8 lines]
>
> anyway to correct that?

Reply to this Message

Lester Mosley - 07 Aug 2007 16:21 GMT

> If you don't force to text and just let Excel decide you could use
>
[quoted text clipped - 30 lines]
>
> - Show quoted text -

I have it working as text except when times are under 1 minuts ie:
00:00:09 or i noticed 1:01:09 caused itto act up as wel using the
equasion from yesterday

Reply to this Message

Lester Mosley - 13 Aug 2007 16:13 GMT

I am still have large issues with converting a large time in hours
like (staff time) 15009:11:42 to seconds to get it to add to other
times of the same nature.

I have had the previous forumals not work with repating numbers ie:
42:42:42 or work with any time under 1 minuts ie: 00:00:42

Any idea how to get it to see the times? i need this help please.

Reply to this Message

Peo Sjoblom - 13 Aug 2007 17:12 GMT

It's because 42:42:42 is time by Excel's standard so if you have
15009:11:42 in A1 and 42:42:42 in B1 just use

=LEFT(A1,FIND(":",A1)-1)/24+SUBSTITUTE(A1,LEFT(A1,FIND(":",A1)),"")/60+C1

then format as [hh]:mm:ss

I got 15051:54:24 which seems to be correct

if you want to do this in one fell swoop enter it as

=15009/24+"0:11:42"+"42:42:42"

format as [hh]:mm:ss

and it will also return 15051:54:24

Signature

Regards,

Peo Sjoblom

>I am still have large issues with converting a large time in hours
> like (staff time) 15009:11:42 to seconds to get it to add to other
[quoted text clipped - 4 lines]
>
> Any idea how to get it to see the times? i need this help please.

Reply to this Message