I try to add the amount of 100 milliseconds to a time stamp that is formatted as YYYY-MM-DD HH:MM:SS:SSS, e.g. in cell A1 there is written 2025-08-20 15:00:00:500
To add 100 ms to this date, in B1 I write =A1+100/(24*60*60*1000).
Unfortunately this doesn’t work and I always receive the #VALUE! error.
How can I add 100 ms to the date in A1?
That suggests that A1 actually contains a text string rather than a date value. Check with View → Value Highlighting.
this is Textstring but not a Date, because you have to use your [Decimalpoint|Decimalkomma] as delimiter between Seconds and Milliseconds.
use Format-code yyyy-mm-dd HH:MM:SS,000 and Formula:
=A1+100/1000/86400 ## add 100 milliseconds
It’s neither a simple nor a common way to accept ‘00:500’ as INPUT, to calculate with such a structure — in your example, ‘00:500+00:100’ — and to output the corresponding structure ‘00:600’ formally and mathematically correctly as OUTPUT. Note that this one-off task becomes confusing when there’s one or more overflows/carry over like ‘00:500+00:750’ = ‘00:1250’???
One of my initial solutions is the fairly general one: split each block of numbers into individual cells with '=REGEX(A1;"[\d]{1,4}";;n), where n is the position of the respective block of numbers and {1,4} searches for and outputs the number of characters in a block from 1× to 4×, so that you get 7 cells, regardless of which characters separate them in your TEXT string, from year to millisecond. ‘[\d]’ only searches for what is a digit ‘0–9’. These pseudo-number blocks, still formatted as text, can be converted to numeric format using ‘=DATE()’ and ‘=TiME()’, as @Lupp and @fpy ave described.
Admittedly, I refuse to publish my test file, as it temptingly offers a vague solution that already has a flaw in its definition, and its OUTPUT is flawed as well, albeit only visually without further calculation.
I don’t know your reason for insisting on your special INPUT ‘00:000’ without a comma/point!?