Rounding Seconds in 4/21/2010 4:30:23 PM

[Tom Langley]

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.

 

[Joe User]

=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 -----

 

[Joe User]

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 -----

 

[Joe User]

Not-so minor typo....

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 -----

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 -----