Assist in VBA Code using loop in EXCEL

[wesleypeter]

I have date and time in the format "01/00/1900 7:47:41 AM" from cell F2 to F80. The date remains teh same in every cells but the time varies. I want to use a macro to convert the every date to "01/01/1900" and keep the time unchanged. I use Excel 2003.

Can anyone assist me with the macro. Revert for further queries if any.

 

[Datsmart]

If you have "01/00/1900 7:47:41 AM" in a cell, it is not a true date and time. It is text. There is no such date as "01/00/1900".
Just for reference, would "01/00/1900" be month zero or day zero?

 

[wesleypeter]

Thank you John for your reply. I know that the date is not a realy one but just a dummy date displayed. But my main concern is about the time displayed. "01/00/1900 7:47:41 AM" is the format that I receive from a report that I pull from an application. Is it possible to change only the date to " 01/01/1900" and keep the time intact? If so, please assist.

 

[silverbird]

VBA excel sees date in the number of days after 1/1/1900. Time, thus is stored in the decimal part. Therefore, if you only take the decimals and put it into a cell with date format, you will only get a time with date 1/1/1900. (I don't think John's case is necessarily true, though it is completely possible)

Read more on:
http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

____________________________________________________________

If what John says it true (i.e. it is text), it is still very easy to manipulate the string by using these functions:
- Split
- Left
- Right
It will take a little long to run if there are lots of data, but it is doable.

Read about these functions on:
http://www.techonthenet.com/excel/formulas/index_vba_alpha.php (Left right)
http://visualbasic.about.com/od/learnvb6/l/blvbsplit.htm (Split)