Yes, that might work for you, Joe, but it wouldn't work for me because my
Windows Regional Options (in Control Panel, not in Excel) aren't set to
m/dd/yyyy.

For me, & for others with similar Windows settings, your formula would need
to be changed to =--TEXT(A1+TIME(0,0,30),"d/mm/yyyy hh:mm")
--
David Biddulph


"Joe User" <joeu2004 wrote in message
...
"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.


.