Changing formatted dates to true values
Posted by Dawn Rizzo on February 12, 2002 11:37 AM
I have comma delimited text that I am importing into Excel. The file inmcludes a colunm that has month, day, year, hour, minute second (mm/dd/yyyy hh:mm:ss).
We need to drop the seconds and round to the nearest minute. We can get it to do this with custom formatting, but we need to make the new value the TRUE value. We have tried Copy, Paste Special to paste just the value into another column, but it pastes it as the date serial number and keeps the seconds.
Has anyone been successful in converting a date format into the true value?
Posted by Mark W. on February 12, 2002 12:52 PM
For a datetime value in cell A2 use =FLOOR(A2,1/1440) [nt]
Posted by Mark W. on February 12, 2002 2:04 PM
Hmmm, just noticed that you said, "round to the nearest minute"...
...perhaps, =MROUND(A2,1/1440), would be better.
You'll need to add the Analyis ToolPak for this
function.
Posted by Mark W. on February 12, 2002 2:58 PM
Or, following IML's lead...
at 22033a.html
use =ROUND(A1*1440,0)/1440 in lieu of relying on
the Analyis ToolPak.
