VBA convert date to text

[Shadkng]

Hi, in the below snippet of code I need help in revising the code so range ("G15"), which is a date can be changed to text. Currently when we enter the date in that field we do so as 052324 and it changes to appear as 5/23/24. But when the date moves to the outlook email it shows as 52324. I want it to appear as 5/23/24 if possible. Thanks for the help.

With oMail
        .SentOnBehalfOfName = "sales@rfbcorp.com"
        .To = Worksheets("DETAIL FORM").Range("G3")
        .cc = ""
        .HTMLBody = "See attached order confirmation. Your order will be released for production. Please notify us immediately if any changes are needed." & "<br/>" & "<br/>" _
                   & "Estimated ship date: " & Sheets("FINALORDER").Range("G15") & "<br/>" & "<br/>" _
                   & "Thank You" & "<br/>" & "<br/>" _

 

[MARK858]

What happens if you change

Sheets("FINALORDER").Range("G15")

to

Format(Sheets("FINALORDER").Range("G15"), "m/dd/yyyy")

If not what is causing the change in the below?
and it changes to appear as 5/23/24.

 

[Shadkng]

What happens if you change

Sheets("FINALORDER").Range("G15")

to

Format(Sheets("FINALORDER").Range("G15"), "m/dd/yyyy")

If not what is causing the change in the below?
and it changes to appear as 5/23/24.

I got the following weird date

Estimated ship date: 8/10/1964

 

[Shadkng]

I got the following weird date

Estimated ship date: 8/10/1964

 

[MARK858]

Which is why I asked....

If not what is causing the change in the below?
and it changes to appear as 5/23/24.

 

[Shadkng]

I'm sorry, I don't understand your reply

 

[MARK858]

What in your cell is converting the number 052324 to appear as a date 5/23/24?

 

[MARK858]

Coming at it another way try (but it is dependent on the answer to my previous question)...

Dim Holder As String
Holder = Left(Sheets("FINALORDER").Range("G15"), Len(Sheets("FINALORDER").Range("G15")) - 4) & "/" & Mid(Sheets("FINALORDER").Range("G15"), Len(Sheets("FINALORDER").Range("G15")) - 3, 2) & "/" & Right(Sheets("FINALORDER").Range("G15"), 2)

With oMail
        .SentOnBehalfOfName = "sales@rfbcorp.com"
        .To = Worksheets("DETAIL FORM").Range("G3")
        .cc = ""
        .HTMLBody = "See attached order confirmation. Your order will be released for production. Please notify us immediately if any changes are needed." & "<br/>" & "<br/>" _
                   & "Estimated ship date: " & Holder & "<br/>" & "<br/>" _
                   & "Thank You" & "<br/>" & "<br/>" _

 

[Shadkng]

Coming at it another way try (but it is dependent on the answer to my previous question)...

Dim Holder As String
Holder = Left(Sheets("FINALORDER").Range("G15"), Len(Sheets("FINALORDER").Range("G15")) - 4) & "/" & Mid(Sheets("FINALORDER").Range("G15"), Len(Sheets("FINALORDER").Range("G15")) - 3, 2) & "/" & Right(Sheets("FINALORDER").Range("G15"), 2)

With oMail
        .SentOnBehalfOfName = "sales@rfbcorp.com"
        .To = Worksheets("DETAIL FORM").Range("G3")
        .cc = ""
        .HTMLBody = "See attached order confirmation. Your order will be released for production. Please notify us immediately if any changes are needed." & "<br/>" & "<br/>" _
                   & "Estimated ship date: " & Holder & "<br/>" & "<br/>" _
                   & "Thank You" & "<br/>" & "<br/>" _

It works now. I see that you are adding back the slashes. Good enough for me. Thanks!

To answer your previous question:
What in your cell is converting the number 052324 to appear as a date 5/23/24?

I'm using a custom format for that cell: ##"/'##'/'##. this allows an easy entry of 052324 and formats it with the slashes.

 

[MARK858]

You're welcome