Adding large times in excel 2003

[Lester Mosley]

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

 

[Peo Sjoblom]

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

 

[Lester Mosley]

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?

 

[Peo Sjoblom]

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.

 

[Lester Mosley]

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


I appreciate your help!

 

[Peo Sjoblom]

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

 

[Dave Peterson]

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

Then use another column to do the arithmetic.

 

[Peo Sjoblom]

Blasted!


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


Peo

 

[Lester Mosley]

=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

 

[Lester Mosley]

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

 

[Peo Sjoblom]

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



--
Regards,

Peo Sjoblom

 

[Lester Mosley]

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

--
Regards,

Peo Sjoblom








- 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

 

[Lester Mosley]

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.

 

[Peo Sjoblom]

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