Adding large times in excel 2003

  • Thread starter Thread starter Lester Mosley
  • Start date Start date
L

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
 
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
 
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)
Click to expand...

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
Click to expand...

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

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


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.
 
Thanks. I do need to have a YTD and a overall organization totals so
this should help a lot now.


I appreciate your help!
 
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
 
You may want to use separate columns for your data entry.

Then use another column to do the arithmetic.
 
=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
 
=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
Click to expand...

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?
 
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
 
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 -
Click to expand...

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
 
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.
 
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
 
Back
Top