David Biddulph said:
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 -----