software

I am trying to combine a date/time reference (B10) with another column (C10)
containing milliseconds. I would like to display the date/time and the
milliseconds in another column (D10) like dd/mm/yyyy hh:mm:ss.000.

I have tried the following, but none seems to work:
=DATEVALUE(B10) TIMEVALUE(B10) TIME(0,0,C10)
=B10 TIME(0,0,C10)

Both formulas result appears the same as the reference time in B10 even
after selecting the custom format of dd/mm/yyyy hh:mm:ss.000.

Appreciate a suggestion.

Thanks,
BruceHi Bruce,

If C10 is 1 millisecond, what value does it have? 1 or .001?
What are the values of B10 and C10 and what results do you get from the formulas shown?

--
Kind regards,

Niek Otten

quot;Brucequot; gt; wrote in message news
|I am trying to combine a date/time reference (B10) with another column (C10)
| containing milliseconds. I would like to display the date/time and the
| milliseconds in another column (D10) like dd/mm/yyyy hh:mm:ss.000.
|
| I have tried the following, but none seems to work:
| =DATEVALUE(B10) TIMEVALUE(B10) TIME(0,0,C10)
| =B10 TIME(0,0,C10)
|
| Both formulas result appears the same as the reference time in B10 even
| after selecting the custom format of dd/mm/yyyy hh:mm:ss.000.
|
| Appreciate a suggestion.
|
| Thanks,
| Bruce
|
Enter in D10, formatted like dd/mm/yyyy hh:mm:ss.000
=B10 (C10/86400)

Regards,
Stefi

?ruce??ezt ?rta:

gt; I am trying to combine a date/time reference (B10) with another column (C10)
gt; containing milliseconds. I would like to display the date/time and the
gt; milliseconds in another column (D10) like dd/mm/yyyy hh:mm:ss.000.
gt;
gt; I have tried the following, but none seems to work:
gt; =DATEVALUE(B10) TIMEVALUE(B10) TIME(0,0,C10)
gt; =B10 TIME(0,0,C10)
gt;
gt; Both formulas result appears the same as the reference time in B10 even
gt; after selecting the custom format of dd/mm/yyyy hh:mm:ss.000.
gt;
gt; Appreciate a suggestion.
gt;
gt; Thanks,
gt; Bruce
gt;

Yes, I forgot to tell that I supposed in C10 0.001!
Stefi?tefi??ezt ?rta:

gt; Enter in D10, formatted like dd/mm/yyyy hh:mm:ss.000
gt; =B10 (C10/86400)
gt;
gt; Regards,
gt; Stefi
gt;
gt; ?ruce??ezt ?rta:
gt;
gt; gt; I am trying to combine a date/time reference (B10) with another column (C10)
gt; gt; containing milliseconds. I would like to display the date/time and the
gt; gt; milliseconds in another column (D10) like dd/mm/yyyy hh:mm:ss.000.
gt; gt;
gt; gt; I have tried the following, but none seems to work:
gt; gt; =DATEVALUE(B10) TIMEVALUE(B10) TIME(0,0,C10)
gt; gt; =B10 TIME(0,0,C10)
gt; gt;
gt; gt; Both formulas result appears the same as the reference time in B10 even
gt; gt; after selecting the custom format of dd/mm/yyyy hh:mm:ss.000.
gt; gt;
gt; gt; Appreciate a suggestion.
gt; gt;
gt; gt; Thanks,
gt; gt; Bruce
gt; gt;

Hi Stefi,

Your suggestion worked! The date/time reference was actually a text string
and not a serial number as I thought. Therefore, once I converted it using
the datevalue() and timevalue() functions and using your suggestion
C10/86400, it worked! Here is my final formula:
=DATEVALUE(B10) TIMEVALUE(B10) C10/86400
using custom format dd/mm/yyyy hh:mm:ss.000
RESULT: 04/21/06 11:16:34.799

Thanks for your help!
Bruce

quot;Stefiquot; wrote:

gt; Yes, I forgot to tell that I supposed in C10 0.001!
gt; Stefi
gt;
gt;
gt; ?tefi??ezt ?rta:
gt;
gt; gt; Enter in D10, formatted like dd/mm/yyyy hh:mm:ss.000
gt; gt; =B10 (C10/86400)
gt; gt;
gt; gt; Regards,
gt; gt; Stefi
gt; gt;
gt; gt; ?ruce??ezt ?rta:
gt; gt;
gt; gt; gt; I am trying to combine a date/time reference (B10) with another column (C10)
gt; gt; gt; containing milliseconds. I would like to display the date/time and the
gt; gt; gt; milliseconds in another column (D10) like dd/mm/yyyy hh:mm:ss.000.
gt; gt; gt;
gt; gt; gt; I have tried the following, but none seems to work:
gt; gt; gt; =DATEVALUE(B10) TIMEVALUE(B10) TIME(0,0,C10)
gt; gt; gt; =B10 TIME(0,0,C10)
gt; gt; gt;
gt; gt; gt; Both formulas result appears the same as the reference time in B10 even
gt; gt; gt; after selecting the custom format of dd/mm/yyyy hh:mm:ss.000.
gt; gt; gt;
gt; gt; gt; Appreciate a suggestion.
gt; gt; gt;
gt; gt; gt; Thanks,
gt; gt; gt; Bruce
gt; gt; gt;

Hi Bruce,

You are welcome, thanks for the feedback! Your case shows that it's
advisable to check data formats instead of guessing them!

Regards,
Stefi?ruce??ezt ?rta:

gt; Hi Stefi,
gt;
gt; Your suggestion worked! The date/time reference was actually a text string
gt; and not a serial number as I thought. Therefore, once I converted it using
gt; the datevalue() and timevalue() functions and using your suggestion
gt; C10/86400, it worked! Here is my final formula:
gt; =DATEVALUE(B10) TIMEVALUE(B10) C10/86400
gt; using custom format dd/mm/yyyy hh:mm:ss.000
gt; RESULT: 04/21/06 11:16:34.799
gt;
gt; Thanks for your help!
gt; Bruce
gt;
gt; quot;Stefiquot; wrote:
gt;
gt; gt; Yes, I forgot to tell that I supposed in C10 0.001!
gt; gt; Stefi
gt; gt;
gt; gt;
gt; gt; ?tefi??ezt ?rta:
gt; gt;
gt; gt; gt; Enter in D10, formatted like dd/mm/yyyy hh:mm:ss.000
gt; gt; gt; =B10 (C10/86400)
gt; gt; gt;
gt; gt; gt; Regards,
gt; gt; gt; Stefi
gt; gt; gt;
gt; gt; gt; ?ruce??ezt ?rta:
gt; gt; gt;
gt; gt; gt; gt; I am trying to combine a date/time reference (B10) with another column (C10)
gt; gt; gt; gt; containing milliseconds. I would like to display the date/time and the
gt; gt; gt; gt; milliseconds in another column (D10) like dd/mm/yyyy hh:mm:ss.000.
gt; gt; gt; gt;
gt; gt; gt; gt; I have tried the following, but none seems to work:
gt; gt; gt; gt; =DATEVALUE(B10) TIMEVALUE(B10) TIME(0,0,C10)
gt; gt; gt; gt; =B10 TIME(0,0,C10)
gt; gt; gt; gt;
gt; gt; gt; gt; Both formulas result appears the same as the reference time in B10 even
gt; gt; gt; gt; after selecting the custom format of dd/mm/yyyy hh:mm:ss.000.
gt; gt; gt; gt;
gt; gt; gt; gt; Appreciate a suggestion.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks,
gt; gt; gt; gt; Bruce
gt; gt; gt; gt;