I need a macros to replace the dates in Column C which is in the format (MM/DD/YY HH:MM AM/PM) to the format (MM/DD/YYYY).
Basically I want to replace the contents of Column C with just the date excluding the time.
And I want this to be done dynamically, if more rowas are added or removed it sgould do it automatically.
I have attached an example file with the raw data.
This isn't much of a macro project.... highlight column C and set the formatting to Date: MM/DD/YY or Custom: MM/DD/YY
The numbers in the cells still have times, they're just hidden. Formatting will take care of the whole column for you.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Ah, I see, the column C isn't really numeric, it's text.
Please Login or Register to view this content.
Hello Jerry!
Thanks a lot for the VBA Code.
Works fine!
If you can, please explain how your VBA code works as I am a newbie to Excel Macro and VBA.
1) It spots how many rows of data there are and stores that last row in a variable LR.
2) Puts a formula in column D from D2 down to the lastrow. THe formula is =INT(C2)
This formula takes the text string in C2, converts it to numbers, then takes only the whole number value (date), stripping off the decimal value (hours)
3) It copies the VALUES now listed in column D and puts them in column C to replace the original text strings
4) Removes the formulas entered into column D
5) Applies the Date format you wanted to column C
If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.
Thank you Jerry!
That helped me lots!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks