|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 31st, 2005, 04:10 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
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 
|
|
#2
December 31st, 2005, 06:24 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
|
|
#3
December 31st, 2005, 06:27 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
|
|
#4
December 31st, 2005, 10:26 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
|
|
#5
December 31st, 2005, 10:56 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
|
|
#6
January 1st, 2006, 04:59 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
|
|
#7
January 1st, 2006, 05:08 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Adding Date/Time fields
and format the result as time it that is not how you see it.
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Add New Field to DB | Karen | Database Design | 7 | October 19th, 2005 08:03 PM |
| Adding extra text fields on master | jbird | Powerpoint | 1 | August 29th, 2005 06:41 PM |
| Adding fields in a query | Dave | Running & Setting Up Queries | 4 | November 30th, 2004 12:45 AM |
| adding multiple fields on form | Dan | New Users | 2 | November 24th, 2004 09:54 PM |
| Adding fields in a table | tpitman | General Discussion | 0 | June 20th, 2004 05:08 PM |
Linear Mode
