Okay. My previouse post has been deleted for no reason so I will try again. I am trying to round up a value in cell E5. I tried =ROUNDUP(SUM(D3-NOW()))

But that is an error.

And what value would you expect to see for
=SUM(D3-Now())

The Sum formula would be expecting multiple number values separated by commas or perhaps a range of cells. You are giving it a single value so Sum is not required. The formula would still fail if D3 doesn't have a number value (eg, it is a string).

When compound formulas fail, test each constituent part to work out why it isn't working.

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

ROUNDUP() must have 2 properties! And the second one (num_digits) is not optional!
So ROUNDUP(A2) doesn't work, ROUNDUP(A2,1) does work!

SUM of the cell containing a string returns 0, not an error! I often use this, when I need some formula to handle any text values as zeros. I.e like SomeFormula(SUM(A2),...)

Good question. Never thought about it properly. Currently today (UK time) it shows 68.64. I supose if it was greater than .5 then round up if lower round down. So rounded to the nearest whole value. I thought just wrapping the whole calculation in a ROUNDUP() function should have worked.

What you're describing is =ROUND, not =ROUNDUP. ROUNDUP always rounds up, ROUNDDOWN always rounds down to the increment you set in number of digits.

There are two other options, commands =INT and =TRUNC. A simple rundown of all of these is available at Int, Round, RoundDown, RoundUp, and Trunc functions - Power Platform | Microsoft Learn.

I'm including a dead-simple little glimpse of how these handle the same one-decimal entries, both positive and negative numbers. You can see the formulas below for the first row. You'll see that INT and TRUNC are identical for positive numbers but different for negative.

Attached Images

round.jpg (44.2 KB, 7 views)

No it dosn't
=(INT(SUM(10.8))) returns 10
=ROUND((INT(SUM(10.8)))) returns an error message. Same as ROUNDUP did.

You entered too few arguments for this function.

I also have no idea why you are using D3-NOW() ? NOW returns date and actual time so it changes every nth of a second... What does D3 contain?
Also be aware NOW is a volatile function

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

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.

You would just use =INT, not =INT(SUM(*), and certainly not =ROUND((INT(SUM(*).

Try =INT(10.8)
and
=ROUND(10.8,0) - where the comma and zero tell it what to round TO (zero decimal places.)

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.