Yes it does change every second but under windows 10, i can't change the clock hours and minutes temperory. I could do in windows 7.

Okay =ROUND(D3-NOW(),0) does seem to work. I will need to wait until tomorrow morning to see as I can't change the hour on my PC.

No, it does not.

NOW() changes whenever Excel recalculates anywhere in the workbook (and the calculation mode is Automatic).

To demonstrate, in a new workbook, format A1 and B1 as Custom m/d/yyyy h:mm:ss.000. Widen columns A:B sufficiently; 3 default column widths should suffice.

(You might choose a different date format.)

Enter =NOW() into A1. Then quickly copy A1 and paste-special-value into B1.

Pasting will probably cause A1 to change. But the difference should be small. For example, I see 9/27/2023 13:40:31.660 in A1 and 9/27/2023 13:40:27.070 in B1, a difference of less than 4 sec.

Now, leave the workbook alone for however long you wish to demonstrate that A1 is not updating automatically.

Finally, cause a recalculation. For example, enter anything new into any other cell.

Now, A1 will be very different from B1. For example, after I typed these instructions, when I enter something into C1, A1 becomes 9/27/2023 13:45:09.000, about 5 min later than B1. QED.

-----
Not really.

ROUND(D3-NOW(),0) rounds the difference to a number of days, assuming that D3 contains an Excel date or date/time value ("serial number") and E3 is formatted as Number (or General?).

A decimal difference of 0.5 is 12 hours. So ostensibly, you just need to enter a difference of 12 hours plus or minus 0 or 1/1000 second into D3 to test the rounding formula.

(1/1000 second because Excel formats time only to the millisecond. But actually, any decimal fraction of a second should suffice.)

So, we might enter =NOW() into A3 and the following formulas, formatting A3:E3 as Custom m/d/yyyy h:mm:ss.000 with sufficiently widened cells.

B3: =A3 - 0.5 - "0:00:0.001"
C3: =A3 - 0.5
D3: =A3 + 0.5
E3: =A3 + 0.5 + "0:00:0.001"
B4:E4: =ROUND(B3-$A$3, 0) formatted as Number with 14 decimal places
A5:E5: =A3 formatted as Number with 10 decimal places
B6:E6: =B3-$A$3 formatted as Number with 15 decimal places

For example, I see.... (click here).



Note that ROUND rounds away from zero (when it rounds up). So -0.5 rounds to -1.

If that is not what you expect, explain your intent and expectations in more detail, along with numerical examples.


PS.... And there is nothing sacrosanct about NOW(). You might enter specific date/time values into A3. For example, you might enter even and odd date "serial numbers" (the integer part) with times 0:00 and 12:00 to convince yourself that Excel does not do "banker's rounding" to even, as VBA does.


And if you want to emulate NOW(), enter times in the form h:mm:ss.ff0, where "ff0" is some number of 1/100 seconds; for example, 1:23:45.670. 1/100 because NOW() rounds down current time to the 1/100 second. Note that unfortunately, we cannot see or edit the fractional seconds in the Formula Bar; and the time displayed in the Formula Bar is rounded to the second.