If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Rounding Seconds in 4/21/2010 4:30:23 PM
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. |
#2
|
|||
|
|||
Rounding Seconds in 4/21/2010 4:30:23 PM
=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. |
#3
|
|||
|
|||
Rounding Seconds in 4/21/2010 4:30:23 PM
"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. . |
#4
|
|||
|
|||
Rounding Seconds in 4/21/2010 4:30:23 PM
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. . |
#5
|
|||
|
|||
Rounding Seconds in 4/21/2010 4:30:23 PM
"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 ... 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. |
#6
|
|||
|
|||
Rounding Seconds in 4/21/2010 4:30:23 PM
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 ... "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 ... 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. |
Thread Tools | |
Display Modes | |
|
|