(basic level issue) Once upon a time, I wanted to keep Date formatting! :)

[ianquiksilver]

Hello all! I'm a basic VBA macro-er and after going through plenty search-results trying to find a solution to my problem I finally opted for posting on a forum. So please don't get too impatient with me when you see how basic my question might be.

Its regarding dates and keeping the formatting.
So if I have a date given as "19/06/2017" and I change it to "20170619" by feeding "YYYYMMDD" into the custom formatting thingy that's fine. Now, I would like the actual value of my cell to be changed to be equal to how it looks like with the new formatting, because if I click on the cell that shows "20170619" in my sheet, the Formula Bar shows the "actual" value of the cell being "19/06/2017" instead of "20170619" (see screenshot below), meaning that if I run any code on it, it will consider the real value of the cell to be 19/06/2017 and so the result of any calculation made on them will come out 'wrong'. None of the Special Pastes I tried seemed to achieve this. For example, if I "Paste Value" the value shown below into the cell next to it, it comes out as "42905" (and I have no idea why)

The main reason for this is to have "19/06/2017" converted to "20170619" and passed as a string so I can remove the last 2 digits with MID(dateValue; 1; 6) and then add "01" at the end to make all my dates into the "first of the month", keeping the YYYYMMDD format.

Thanks for your help and patience!
Best regards,
Kris A.

 

[ianquiksilver]

Wow, that was literally what I said after running your Sub. Perfect and flawless! :D Everything I had tried today on this issue seems quite messy in comparison to say the least. Thanks again so much!! I really need to learn more about VBA I haven't taught myself the "With.." convention, I'll google it and find some tutorials, but just in case you know of one which is really good, maybe tucked away in your favorites, please let me know
Also, thanks so much for taking the time and trouble of actually writing me a full Sub! :D Just for fun I put your name in comments next to that line in my code to recognize your contribution

 

[Peter_SSs]

Glad it worked out well for you.