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.

=MROUND(A1,TIME(0,1,0))

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


"Tom Langley" <TomLangley@discussions.microsoft.com> wrote in message 
news:4396E037-F1A9-40E8-8A62-B7BAD372B248@microsoft.com...
> 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. 

"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" <TomLangley@discussions.microsoft.com> wrote in message 
> news:4396E037-F1A9-40E8-8A62-B7BAD372B248@microsoft.com...
> > 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. 
> 
> 
> .
> 

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 
news:5B3BEB77-A960-47B2-A2B8-5A67CD23E83F@microsoft.com...
> "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" <TomLangley@discussions.microsoft.com> wrote in message
>> news:4396E037-F1A9-40E8-8A62-B7BAD372B248@microsoft.com...
>> > 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.
>>
>>
>> .
>> 

"David Biddulph" <groups [at] biddulph.org.uk> wrote:
> 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")

That is a valid point.  Others might need to use yyyy/m/d for the date part.
And there may be other regional date forms that I am not familiar with.

Tom can decide which trade-off is more important to him.  Most users of
these forums develop formulas for themselves.  So a solution tailored for
their regional specifications might suffice.  On the other hand, everyone
gets bitten by floating-point aberrations at one time or another.

But MROUND(A1,TIME(0,1,0)) is not reliable.

For example, if A1 is 4/22/2010 11:30:30 PM, the MROUND formula results in
the time 11:30:00 instead of 11:31:00.

A more reliable alternative is ROUND(A1*1440,0)/1440, which Tom would format
with Custom "m/d/yyyy h:mm:ss AM/PM" without quotes.

That does appear to "work" for all date/times between 4/22/2010 and
4/22/2011.  By "work", I mean:  it returns the same floating-point
representation as the rounded date/time entered manually.

However, I am relunctant to recommend the ROUND formula because it does
exhibit floating-point aberrations for some time-only data.  I suspect that
is also true for some (early?) date/time data.

For example, when A1 is just 00:12:30 (no date), the result of the ROUND
formula is one bit off in the least-significant bit when compared to the
same rounded time entered manually in B1 (e.g).

Normally, we cannot see differences in the least-significant bit because of
Excel's display formatting limitations.  Both appear to be 0.00625 when
formatted as Number with 17 decimal places.

And the infinitesimal difference might be masked in some formulas by Excel's
heuristics, poorly described in KB 78113 at support.microsoft.com/kb/78113.
For example, IF(A1=B1,TRUE) returns TRUE.

But an expression like (A1-B1)*1440 does not result in zero.

In contrast, I would expect the TEXT formula to always "work" (as defined
above) because the interpretation of the string returned by TEXT should be
the same as the interpretation of the text entered manually.

But aarrgghh!  I just learned that the operative word is "should".

The TEXT formula does seem to "work" for all date/times between 4/22/2010
and 4/22/2011.

But when A1 contains just 00:08:30 (no date), the result
of --TEXT(A1+TIME(0,0,30),"hh:mm") [1] is one bit off in the
least-significant bit when compared to the rounded time entered manually.

That is, --"09:00" does not result in the same floating-point representation
as when we enter 09:00 manually.  Neither does VALUE("00:09").  (But
Range("A1")="00:09" does.)

At least, that is the case with Excel 2003 SP3.

Because of that defect in TEXT (IMHO), and since the TEXT solution is more
difficult to use because we must use different formats for date/time v.
time-only data, I concede that in general, there seems to be benefit to
using a TEXT formula instead of the formula ROUND(A1*1440,0)/1440.

Both can result in values that are infinitesimally different than the 
equivalent data entry.


-----
Endnotes

[1] For time-only data, we must change the format string used in the TEXT
expression.  This is because Excel does not recognize the date 1/0/1900,
even though that is the date that Excel displays in formats like m/d/yyyy.
Sigh.


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

"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
news:Ydidnd7jsMUOnU3WnZ2dnUVZ8tGdnZ2d@bt.com...
> 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
> news:5B3BEB77-A960-47B2-A2B8-5A67CD23E83F@microsoft.com...
>> "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" <TomLangley@discussions.microsoft.com> wrote in message
>>> news:4396E037-F1A9-40E8-8A62-B7BAD372B248@microsoft.com...
>>> > 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.

Not-so minor typo....

I wrote:
> there seems to be benefit to using a TEXT formula
> instead of the formula ROUND(A1*1440,0)/1440.

I meant:  there seems to be __no__ benefit.

Hmm, I need a "spelling checker" that will flag the absence of "no" and 
"not" when they are intended. :-) :-) :-)


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

"Joe User" <joeu2004> wrote in message 
news:ufWQn8%234KHA.620@TK2MSFTNGP02.phx.gbl...
> "David Biddulph" <groups [at] biddulph.org.uk> wrote:
>> 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")
>
> That is a valid point.  Others might need to use yyyy/m/d for the date 
> part.
> And there may be other regional date forms that I am not familiar with.
>
> Tom can decide which trade-off is more important to him.  Most users of
> these forums develop formulas for themselves.  So a solution tailored for
> their regional specifications might suffice.  On the other hand, everyone
> gets bitten by floating-point aberrations at one time or another.
>
> But MROUND(A1,TIME(0,1,0)) is not reliable.
>
> For example, if A1 is 4/22/2010 11:30:30 PM, the MROUND formula results in
> the time 11:30:00 instead of 11:31:00.
>
> A more reliable alternative is ROUND(A1*1440,0)/1440, which Tom would 
> format
> with Custom "m/d/yyyy h:mm:ss AM/PM" without quotes.
>
> That does appear to "work" for all date/times between 4/22/2010 and
> 4/22/2011.  By "work", I mean:  it returns the same floating-point
> representation as the rounded date/time entered manually.
>
> However, I am relunctant to recommend the ROUND formula because it does
> exhibit floating-point aberrations for some time-only data.  I suspect 
> that
> is also true for some (early?) date/time data.
>
> For example, when A1 is just 00:12:30 (no date), the result of the ROUND
> formula is one bit off in the least-significant bit when compared to the
> same rounded time entered manually in B1 (e.g).
>
> Normally, we cannot see differences in the least-significant bit because 
> of
> Excel's display formatting limitations.  Both appear to be 0.00625 when
> formatted as Number with 17 decimal places.
>
> And the infinitesimal difference might be masked in some formulas by 
> Excel's
> heuristics, poorly described in KB 78113 at 
> support.microsoft.com/kb/78113.
> For example, IF(A1=B1,TRUE) returns TRUE.
>
> But an expression like (A1-B1)*1440 does not result in zero.
>
> In contrast, I would expect the TEXT formula to always "work" (as defined
> above) because the interpretation of the string returned by TEXT should be
> the same as the interpretation of the text entered manually.
>
> But aarrgghh!  I just learned that the operative word is "should".
>
> The TEXT formula does seem to "work" for all date/times between 4/22/2010
> and 4/22/2011.
>
> But when A1 contains just 00:08:30 (no date), the result
> of --TEXT(A1+TIME(0,0,30),"hh:mm") [1] is one bit off in the
> least-significant bit when compared to the rounded time entered manually.
>
> That is, --"09:00" does not result in the same floating-point 
> representation
> as when we enter 09:00 manually.  Neither does VALUE("00:09").  (But
> Range("A1")="00:09" does.)
>
> At least, that is the case with Excel 2003 SP3.
>
> Because of that defect in TEXT (IMHO), and since the TEXT solution is more
> difficult to use because we must use different formats for date/time v.
> time-only data, I concede that in general, there seems to be benefit to
> using a TEXT formula instead of the formula ROUND(A1*1440,0)/1440.
>
> Both can result in values that are infinitesimally different than the 
> equivalent data entry.
>
>
> -----
> Endnotes
>
> [1] For time-only data, we must change the format string used in the TEXT
> expression.  This is because Excel does not recognize the date 1/0/1900,
> even though that is the date that Excel displays in formats like m/d/yyyy.
> Sigh.
>
>
> ----- original message -----
>
> "David Biddulph" <groups [at] biddulph.org.uk> wrote in message
> news:Ydidnd7jsMUOnU3WnZ2dnUVZ8tGdnZ2d@bt.com...
>> 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
>> news:5B3BEB77-A960-47B2-A2B8-5A67CD23E83F@microsoft.com...
>>> "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" <TomLangley@discussions.microsoft.com> wrote in message
>>>> news:4396E037-F1A9-40E8-8A62-B7BAD372B248@microsoft.com...
>>>> > 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.
>