(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]

At the moment the only way I can do what I need to, is change the date formatting to YYYYMMDD in Excel, then copy-paste the column into Notepad, then copy-paste back into Excel

 

[Peter_SSs]

Welcome to the MrExcel board!

One way would be to use the formula in cell B2: =TEXT(A2,"yyyymmdd")
Then copy B2 (or all of column B) and Paste Special (Values). You could even do this past back over the data in column A if you want it replaced.

 

[NARAYANK991]

Hi ,

If you are already using formulae to remove the last two digits of your date , after it has been converted to the YYYYMMDD format , then why not use a formula to do this before ?

=EOMONTH(date value , -1) + 1

will always return the date of the first day of the month.

 

[ianquiksilver]

Brilliant! Thanks so much Peter! :D You gave me a two-in-one solution (plus it also works in VBA :D)

 

[ianquiksilver]

Thanks so much for your input, I'll definitely use that formula instead of what I was doing with removing the "day" portion and pre-pending "01"
Thanks for all the input, I'm most grateful and if you both lived closer to me I'd definitely buy you dinner :D

 

[ianquiksilver]

Final solution being a combo of both : (Assuming the original date value in the DD/MM/YYYY format is in A2) --> =TEXT(EOMONTH(A2;-1) + 1;"yyyymmdd")

 

[Peter_SSs]

Thanks so much Peter! :D You gave me a two-in-one solution (plus it also works in VBA :D)

You are welcome.

I didn't read your question well enough though & NARAYANK991's post made me re-think. So, another formula would be:
=TEXT(A2,"yyyymm")&"01"

.. or with vba to replace the column A values all at once using this concept:

Sub ChangeDates()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .NumberFormat = "General"
    .Value = Evaluate(Replace("if(len(#),text(#,""yyyymm"")&""01"","""")", "#", .Address))
  End With
End Sub

 

[ianquiksilver]

Can i pick your guy's awesome brains just for one more thing, its related to the same issue, just a couple steps further.
I was having some complications transferring the formula to VBA, first I had to append "application." to the Eomonth function, then I had to change TEXT function to FORMAT, after which I was getting a type mismatch (probably Eomonth spitting out numbers and Format expecting strings). Anywho, I went for the lazy solution and just opted for ActiveCell.FormulaR1C1 = "=TEXT(EOMONTH(RC[-1],-1)+1,""yyyymmdd"")" which is working fine. This is followed by a 'Paste Values' command from Col. B to C

My issue now is that, what you see in this screenshot is after Column C has undergone the following command Columns("C").NumberFormat = "0" the format type top right shows that they are indeed defined/"considered" as Number type, but I'm still getting the Excel "Help Assistant" (not sure how its called) which offers me the possibility to "Convert to Number". How do I avoid this? I tend to have it sometimes with different types of content editing functions.

And thanks again Peter for further input! I will evaluate my options and see how I can fit it in (excuse the pun, I couldnt help it ^^)

 

[Peter_SSs]

My issue now is that, what you see in this screenshot is after Column C has undergone the following command Columns("C").NumberFormat = "0" the format type top right shows that they are indeed defined/"considered" as Number type, but I'm still getting the Excel "Help Assistant" (not sure how its called) which offers me the possibility to "Convert to Number". How do I avoid this?

You have to set the format of the cell before you put the value in, not after.

Having said that, did you try my code that does (as far as I understand your requirement) all the steps on all the rows at once?
(Also, notice in my code the first point above about setting the cell format before putting the values in)