How do I add add decimal seconds to time

[investorwize]

Working on another real estate application and one of my employees are trying to add decimal seconds to time.

So my column looks like this where column 2 is the amount of seconds passed and is a decimal type

COLUMN1 COLUMN2 COLUMN3
12:32:07 66.22 = COLUMN1 + TIME(0,0,COLUMN2)

This is what I am trying but I believe the calculation comes out incorrect because seconds is a decimal.

Let me know how I can do this.


Geoffrey

 

[shg]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr][tr][td]

1​

[/td][td]

12:32:07​

[/td][td]

66.22​

[/td][td]

12:33:13.22​

[/td][td]C1: =A1+B1/86400[/td][/tr]
[tr][td]

2​

[/td][td][/td][td][/td][td][/td][td]Format of C1: hh:mm:ss.00[/td][/tr]
[/table]

 

[investorwize]

Wow a second time. That was fast. OK I'm trying it now.

Geoffrey

 

[investorwize]

It Worked. You are absolutely amazing. Now one more thing. Is there a way to round the time to the nearest millisecond.

Geoffrey

 

[investorwize]

Sorry I meant round the time to the nearest second.


Geoffrey

 

[shg]

=A1 + round(B1, 0) / 86400

 

[joeu2004]

COLUMN1 COLUMN2 COLUMN3
12:32:07 66.22 = COLUMN1 + TIME(0,0,COLUMN2)

This is what I am trying but I believe the calculation comes out incorrect because seconds is a decimal.

There is nothing wrong with your original formula.

Non-integer seconds are truncated (rounded down). So if "column2" is 66.9999, the result is the same as adding 66, namely 12:33:13.000.

Perhaps your only problem is: be sure to format the value in "column3" as Custom [h]:mm:ss.

Caveat: "column2" is limited to about 32767.999 (9h 6m 7s).

Sorry I meant round the time to the nearest second.

If "column1" is A1 and "column2" is B1, write:

=A1+TIME(0,0,ROUND(B1,0))

But in that case, "column2" is limited to about 32767.499.

PS.... Why would someone working on a "real estate application" need to add time in seconds? Just curious.

 

[investorwize]

Thanks I formatted it and it does work. You guys are awesome. Its working.

 

[shg]

You're welcome.