Excel question

[IanUAE]

I need some help on carrying out a calculation in Excel. Lets take a time say 2 hours but I need to subtract from this a time in minutes, seconds, thousands of seconds and then continue to subtract another time in seconds, minutes and thousand of seconds from this reduced time, repeat until the 2 hours is finished.

eg:
2:00:00:00
-0:2:25:350
=1:57:34:650
-0:02:20:250
=1:55:14:400

I know how to do this with minutes and seconds but not with the additional thousand of seconds.

Anybody know how to do this?

(some of you may guess what this is for)!

[FlossyThePig]

IanUAE said:

I need some help on carrying out a calculation in Excel. Lets take a time say 2 hours but I need to subtract from this a time in minutes, seconds, thousands of seconds and then continue to subtract another time in seconds, minutes and thousand of seconds from this reduced time, repeat until the 2 hours is finished.

eg:
2:00:00:00
-0:2:25:350
=1:57:34:650
-0:02:20:250
=1:55:14:400

I know how to do this with minutes and seconds but not with the additional thousand of seconds.

Anybody know how to do this?

(some of you may guess what this is for)!

I'm currently using Excel 2003.

Use the cutom format hh:mm:ss.000
Note the decimal point not colon separator after seconds

[OneDs]

I believe excel will work to hundredths but not thousandths, actually measuring dates & times has caused numerous issues for me before and is possibly one of the most restrictive bits of the whole application.

Editted, I have just put the following value into hh:mm:ss.000 format and it does change the thousandths digit 40847.62851116, however when using the =now() formula it will only return down to hundreths, so if you want to automate it you may have to think about breaking the numbers down in a macro do the calculation in code and then join them back together.

Edited by OneDs on Monday 31st October 15:11

[IanUAE]

Thank you it also works in Office 2010.