How to combine milliseconds with date/time reference and display?

[Bruce]

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

 

[Niek Otten]

Hi 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

|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
|

 

[Stefi]

Enter in D10, formatted like dd/mm/yyyy hh:mm:ss.000
=B10+(C10/86400)

Regards,
Stefi

„Bruce†ezt írta:

 

[Stefi]

Yes, I forgot to tell that I supposed in C10 0.001!
Stefi


„Stefi†ezt írta:

 

[Bruce]

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

 

[Stefi]

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


„Bruce†ezt írta: