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
|
|||
|
|||
Calculating time in Excel
Hi,
How do I calculate times in Excel with the following syntax: hh.mm.ss.sss - hh.mm.ss.sss e.g 01.59.59.999 minus 01.56.45.345 Please observe that the 3 last digits are 1/100 of a secund so the formula need to take this in to account when calculating correct results. I need this for calculating racetime results in Hill Climbs ;-) Thanks in advance ! Henrik - Denmark www.racetiming.dk --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
One way:
If your values are already entered as times: A1: 01.59.59.999 A2: 01.56.45.345 A3: =A1-A2 === 00.03.14.654 when all are formatted with Format/Cells/Number/Custom hh.mm.ss.000 If instead your values are text, one way: A3: =TIMEVALUE(SUBSTITUTE(SUBSTITUTE(C1,".",":",2),"." ,":",1)) - TIMEVALUE(SUBSTITUTE(SUBSTITUTE(C2,".",":",2),".", ":",1)) again, formatted as hh.mm.ss.000 In article , Hartvig wrote: Hi, How do I calculate times in Excel with the following syntax: hh.mm.ss.sss - hh.mm.ss.sss e.g 01.59.59.999 minus 01.56.45.345 Please observe that the 3 last digits are 1/100 of a secund so the formula need to take this in to account when calculating correct results. I need this for calculating racetime results in Hill Climbs ;-) Thanks in advance ! Henrik - Denmark www.racetiming.dk --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
Thanks,
Since yesterday I found out that the syntax was wrong - the correct syntax in the Danish version of Excel is as follows: hh:mm:ss,000 when I format cells using this formula there is no problem calculating times with 1/100 secund. But now I got a new problem ! How do I sort results with the lowest time on top ??? Yours Sincerely Henrik - Denmark --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formatting Time Field from Excel Data | Copwriter | Mailmerge | 5 | August 22nd, 2004 02:10 PM |
Excel : Calculating Elapsed Time | Joy | Worksheet Functions | 5 | July 20th, 2004 08:39 AM |
Excel Formula Help please | Seventh Day is The Sabbath | Worksheet Functions | 3 | May 22nd, 2004 10:54 AM |
Excel Time Function | Claire | Worksheet Functions | 3 | May 11th, 2004 09:51 PM |
Calculating time differences excluding weekends, holidays | Arvi Laanemets | Worksheet Functions | 0 | December 12th, 2003 02:41 PM |