"David Biddulph" wrote:
=MROUND(A1,TIME(0,1,0)

I would use --TEXT(A1+TIME(0,0,30),"m/dd/yyyy hh:mm") formatted as Custom
"m/dd/yyyy h:mm:ss AM/PM" without quotes.

MROUND and any similar formula using ROUND do not return the exact same
floating-point representation as the above TEXT formula does in some cases.

For example, if A1 has 4/21/2010 11:00:30 PM, B1 has the MROUND formula and
C1 4/21/2010 11:01:00 PM, IF(B1=C1,TRUE) does return TRUE, but
IF(B1-C1=0,TRUE) returns FALSE. Consequently, expressions like B1-C1)*D1
will not return zero as expected.

In contrast, if B1 has the TEXT formula, B1 has the exact same
floating-point representation as C1.

Note: `+TIME(0,0,30)` is needed because Excel truncates seconds when
formatting hh:mm instead of rounding. We can use 30/86400 or 1/2880 instead
of TIME(0,0,30). The floating-point representations are identical.


----- original message -----

"David Biddulph" wrote:
=MROUND(A1,TIME(0,1,0))

If you have problems, read the Excel help for the MROUND function.
--
David Biddulph


"Tom Langley" wrote in message
...
I am trying to round the seconds portion of a column of cells containing
both
date and time.

Currently the cells contains:
4/21/2010 4:30:23 PM
4/21/2010 4:30:45 PM

I'd like the cell to contain:
4/21/2010 4:30:00 PM
4/21/2010 4:31:00 PM

I couldn't figure out how to get ROUND or REPLACE to do this.
Thanks for the help.


.