Adding Date/Time fields
 

12/31/05 - 08 Hrs 10 Mins
01/01/06 - 01 Hrs 00 Mins

How do I add these two fields ? I don't need the date, just adding the
times.


--
teastman
------------------------------------------------------------------------
teastman's Profile: http://www.excelforum.com/member.php...o&userid=30030
View this thread: http://www.excelforum.com/showthread...hreadid=497168

Adding Date/Time fields
 

Assume they are in A1:A2 and the format is always the same with spaces, Hrs,
Mins and -

=SUMPRODUCT(--SUBSTITUTE(SUBSTITUTE(MID(A1:A2,FIND("-",A1:A2)+2,255),"Mins",""),"Hrs",":"))

--
Regards,

Peo Sjoblom

(No private emails please)


"teastman" wrote in
message ...

12/31/05 - 08 Hrs 10 Mins
01/01/06 - 01 Hrs 00 Mins

How do I add these two fields ? I don't need the date, just adding the
times.


--
teastman
------------------------------------------------------------------------
teastman's Profile:
http://www.excelforum.com/member.php...o&userid=30030
View this thread: http://www.excelforum.com/showthread...hreadid=497168

Adding Date/Time fields
 

In A1 and A2 put:
12/31/2005 8:10
1/1/2006 1:00

in A3 put:
=(A1-INT(A1))+(A2-INT(A2)) and format as time 13:30 and you will see:

9:10

--
Gary''s Student


"teastman" wrote:


12/31/05 - 08 Hrs 10 Mins
01/01/06 - 01 Hrs 00 Mins

How do I add these two fields ? I don't need the date, just adding the
times.


--
teastman
------------------------------------------------------------------------
teastman's Profile: http://www.excelforum.com/member.php...o&userid=30030
View this thread: http://www.excelforum.com/showthread...hreadid=497168

Adding Date/Time fields
 

Gary's student, your solution works fine. But if I add more and more
records to add... how do I do a totalled range. For example adding all
columns from a1 to a20.


--
teastman
------------------------------------------------------------------------
teastman's Profile: http://www.excelforum.com/member.php...o&userid=30030
View this thread: http://www.excelforum.com/showthread...hreadid=497168

Adding Date/Time fields
 

1 You really don't need to use INT since it is an extra step, you can use
MOD instead like in

=SUMPRODUCT(MOD(A1:A20,1))

make sure the cell with the formula is formatted as [hh]:mm or else it will
not display times over 24 hours



--
Regards,

Peo Sjoblom

(No private emails please)


"teastman" wrote in
message ...

Gary's student, your solution works fine. But if I add more and more
records to add... how do I do a totalled range. For example adding all
columns from a1 to a20.


--
teastman
------------------------------------------------------------------------
teastman's Profile:
http://www.excelforum.com/member.php...o&userid=30030
View this thread: http://www.excelforum.com/showthread...hreadid=497168

Adding Date/Time fields
 

Assuming they are actually Excel datetime fields.
B2 is the start time
C2 is the finish time

If you just have the time component without the date
=C2-B2-b2+(B2C2)

If you have the date and time (timestamp)
=C2-B2

More information on Date & Time
http://www.mvps.org/dmcritchie/excel/datetime.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"teastman" wrote in message
...

12/31/05 - 08 Hrs 10 Mins
01/01/06 - 01 Hrs 00 Mins

How do I add these two fields ? I don't need the date, just adding the
times.


--
teastman
------------------------------------------------------------------------
teastman's Profile: http://www.excelforum.com/member.php...o&userid=30030
View this thread: http://www.excelforum.com/showthread...hreadid=497168

Adding Date/Time fields
 

and format the result as time it that is not how you see it.