vba convert text to date not always working.

[fvisions]

I have search this website for a code to help me but none seem to work fully. The current process I have is below. The end result in col AN should be "C"space"mm/dd/yyyy" but some are appearing as "C 44131" instead of 10/27/2020.
Col AJ is a text but not actually a date of 10/27/20

What you the experts say?? Thank you in advance for any assistance.

 '  LocationSupp_DateFormat Macro
    
   ' Columns("AJ").Select
   ' Selection.NumberFormat = "General"
    Columns("AV").Select
    Selection.NumberFormat = "General"
    
    Range("AU2").Select
    Application.CutCopyMode = False
    Range("AU2:AU" & LastRow).FormulaR1C1 = "C"
    Range("AU2:AU" & LastRow).Value = Range("AU2:AU" & LastRow).Value
        
    Range("AV2").Select
    Application.CutCopyMode = False
    Range("AV2:AV" & LastRow).FormulaR1C1 = "=TEXT(RC[-12],""mm/dd/yyyy"")"
    Range("AV2:AV" & LastRow).Value = Range("AV2:AV" & LastRow).Value
    Columns("AV:AV").Select
    Selection.NumberFormat = "mm/dd/yyyy"
    Range("AN2:AN" & LastRow).FormulaR1C1 = "=CONCATENATE(RC[7],"" "",RC[8])"
    Range("AN2:AN" & LastRow).Value = Range("AN2:AN" & LastRow).Value

 

[Joe4]

If you are concatenating multiple pieces, you need to explicitly apply the TEXT function to the date piece of it.
You can see how you are applying it to your AV column. Try doing the same to your AN column, specifically the RC[8] piece.
I will leave it to you to do, as it will be good practice.

 

[fvisions]

If you are concatenating multiple pieces, you need to explicitly apply the TEXT function to the date piece of it.
You can see how you are applying it to your AV column. Try doing the same to your AN column, specifically the RC[8] piece.
I will leave it to you to do, as it will be good practice.

I am confused, RC[8] is AV??

 

[Joe4]

So I take it then that you did not create this code yourself. Still, it is good practice to try to understand it and edit it (that is the best way to learn!).

See this line here:

    Range("AV2:AV" & LastRow).FormulaR1C1 = "=TEXT(RC[-12],""mm/dd/yyyy"")"

This is applying the test format to the value in cell RC[-12] (this is just a range reference - see here: An Ultimate Guide To Learn R1C1 Reference Style in Excel)

You need to apply the same logic to the part in red below:

    Range("AN2:AN" & LastRow).FormulaR1C1 = "=CONCATENATE(RC[7],"" "",RC[8])"

 

[fvisions]

So I take it then that you did not create this code yourself. Still, it is good practice to try to understand it and edit it (that is the best way to learn!).

See this line here:

    Range("AV2:AV" & LastRow).FormulaR1C1 = "=TEXT(RC[-12],""mm/dd/yyyy"")"

This is applying the test format to the value in cell RC[-12] (this is just a range reference - see here: An Ultimate Guide To Learn R1C1 Reference Style in Excel)

You need to apply the same logic to the part in red below:

    Range("AN2:AN" & LastRow).FormulaR1C1 = "=CONCATENATE(RC[7],"" "",RC[8])"

Actually I did created this myself. I just don't have master knowledge on how to edit as your are instructing.

 

[Joe4]

How did you get the lines of code I copied out of your code above?
Do you know how to use the TEXT function in Excel?

If you just use the Macro Recorder as you entered the formulas into cells AV2 and AN2 manually, then do just that.
You just need to use the TEXT function on the date piece that you are concatenating.

Give it a try and see how you do (that is the best way to learn)!

 

[fvisions]

How did you get the lines of code I copied out of your code above?
Do you know how to use the TEXT function in Excel?

If you just use the Macro Recorder as you entered the formulas into cells AV2 and AN2 manually, then do just that.
You just need to use the TEXT function on the date piece that you are concatenating.

Give it a try and see how you do (that is the best way to learn)!

To give a bit of insight, this worksheet is sent by other departments. Not all departments process col AJ "date installed" correctly. the formats are not standard and the date is not always a date so on top of all the vba code for multiple process' I am trying to setup a way to read col AJ as a date with the same format as mm/dd/yyyy then it is concatenated with the letter C & a space in front of the date. This is a must for the upload process to a company software. I am creating the VBA with form command button menu for the end user which is not me but those who will be doing the process do not have enough excel knowledge to understand when a date is not a date.

 

[Joe4]

OK, here is the background.

All dates in Excel are stored as formulas, specifically, the number of days since 1/0/1900. Enter any date into Excel, and then change the format of the cell to "General", and you will see the date as Excel does.

On top of that, formulas like CONCATENATE only copy the cell value over, NOT the cell formatting. So all date formatting is lost when you use CONCATENATE.
So "C 44131" is actually presenting "C" followed by a space, followed by a valid date -- it is just the date with no formatting.
How do you get the date format to be used in your formula? You use the TEXT function.
That is what baffled me. You are already used it here:

    Range("AV2:AV" & LastRow).FormulaR1C1 = "=TEXT(RC[-12],""mm/dd/yyyy"")"

So I was assuming that your understood it and knew how to use it.

Then it is just a matter of applying it to this line:

    Range("AN2:AN" & LastRow).FormulaR1C1 = "=CONCATENATE(RC[7],"" "",RC[8])"

by changing it to this:

    Range("AN2:AN" & LastRow).FormulaR1C1 = "=CONCATENATE(RC[7],"" "",TEXT(RC[8],""mm/dd/yyyy""))"