Macro to convert date cells

[bslater]

I need to take a range of cells and convert the values of them into a
different date format. Currently the cells are in the form mm/dd/yyyy
h:mm:ss AM/PM. I need the cell to contain the value of mm/dd/yy
h24:mm. I can't just do a cell format because that doesn't change the
actual values in the cell. I think maybe I need to format the cells to
what I want, copy the values to notepad (or likewise), reformat the
cells to text, and then paste the data back into excel. Anyone have
any idea how to do this? Or if you know a better way, I am all ears!!


Any input would be greatly appreciated, this is driving me crazy!

Brian

 

[Myrna Larson]

I'm not sure what you mean by "mm/dd/yy h24:mm" What is the h24? Do you mean you want the time
in military format?

Is the gist of the problem that the value now includes seconds, and you want to eliminate the
seconds? If so, in another column put the formula =INT(A1*1440)/1440. Copy down as far as
needed, then copy the column of formulas and Edit/Paste Special and select the Values option.

If you want to round to the nearest minute rather than eliminating seconds, the formula would be

=ROUND(A1*1440,0)/1440

 

[bslater]

Thanks for responding Myrna.

Here is exactly what I am trying to do. I am importing data from a
database into excel. In excel I format the data and then save it into
a csv file for later import into another database. When I get the data
originally, dates are in the "mm/dd/yyyy h:mm:ss AM/PM" format. I
need dates to be in military time, with 2 characters for the year. I
used to be able to just do a custom cell format in excel and save the
file, but excel has stopped saving the correct date formats (only a
manual save works). I can do the custom cell format to make the cells
look the way I need, but I need this format to save exactly like that
to a comma delimited file.

I have tried using code I found to export to delimited files, but it
doesn't export the formatted date, it exports the original date value
"mm/dd/yyyy h:mm:ss AM/PM". Any thoughts??

Thanks!