using TIME with decimal seconds?

[MikeLinMonroe]

Newbie warning....

I'm trying to add/subtract a portion of a second using
the TIME function, but it always seems to use
integers...is there a better way? I can get it to
display all the decimal places I want for data entry, but
not add two together like this:

01:02:03.4

subtract 0.2 seconds { =XX-TIME(0,0,0.2) }

Should give 01:02:03.2 but always gives 01:02:03.4


??

Mike

 

[Peo Sjoblom]

Try

=XX-2/24/60/60/10

 

[Norman Harker]

Hi Mike!

An interesting question that led me to look a bit deeper into argument
constraints for TIME and DATE functions. I've not done complete checks
on all versions but in Excel 2003...

Not covered by Help for TIME is the fact that the three arguments
Hour, Minute and Second accept positive and negative integers between
0 and 32767. Any non-integer element in these arguments is truncated.
The only apparent solution is to use Peo's suggestion of direct
calculation of the time using:

=XX-2/24/60/60/10

Since time formats to the third decimal place are allowed perhaps we
can use a general approach of.

=A1+B1/(24*60*60*1000)

But you should be so lucky that you're dealing with TIME and not DATE
function. Not covered by Help is that all arguments are restricted to
positive or negative integers with any decimal element truncated. The
limit to the Years argument is pretty logical and is covered by Help.
Not covered by Help is that the limit to the Months argument is set at
32764. Going beyond that results in a #NUM! error. But read on!

The limit to the Days is set at 32760 but if you exceed that number
you get the date plus 32760 days with no warning that you've exceeded
the limit. Now that must be a bug!

All of the above is yet to be checked against earlier versions than
Excel 2003. But I'm not holding my breath expecting any difference.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.