How to get the date and insert as text in the cell

[Slavio]

Hi everybody. I have a formula: =SUBSTITUTE(SUBSTITUTE(B4;"No";"Nein");"Yes";"Ja")
in the cell but I can't figure out how to get the date and insert as text in the cell.
In cell B4, the date is in the following format: dd.mm.yyyy
In cell C4 must be like text because then, we copy this data via VBA into word.

The result in the cell should be the date inserted as text 06.05.2021

I have already searched the forum
Will you help?

 

[Slavio]

Hooray yabadabadoo I found it:
This article helped me

=TEXT(SUBSTITUTE(SUBSTITUTE(B3;"No";"Nein");"Yes";"Ja");"dd.mm.yyyy")
But it will write it in the word as: 44322
Will you help?

 

[Oddball2020]

On the VBA side you can force it to regard the referenced cell as text, otherwise it'll regard it as the datevalue or something weird. I think it's FORMAT ... can use this on the spreadsheet too.

Easier to deal with on the excel side by merely adding a single quote mark ' before it... Insert into C4

= "'" & B4 (notice single quote between the double quotes)

... which forces the cell to display as text (will get the little green corner hash in the cell)

Then in VBA, pick it up as text using, to be pasted into word as the value of URDATE

Dim URDATE as String
URDATE = Sheets("Your Sheet").Range("C4").Value ' whatever the date cell is.... the VBA sees this as a text

 

[Slavio]

On the VBA side you can force it to regard the referenced cell as text, otherwise it'll regard it as the datevalue or something weird. I think it's FORMAT ... can use this on the spreadsheet too.

Easier to deal with on the excel side by merely adding a single quote mark ' before it... Insert into C4

= "'" & B4 (notice single quote between the double quotes)

... which forces the cell to display as text (will get the little green corner hash in the cell)

Then in VBA, pick it up as text using, to be pasted into word as the value of URDATE

Dim URDATE as String
URDATE = Sheets("Your Sheet").Range("C4").Value ' whatever the date cell is.... the VBA sees this as a text

Thank you for your response. I did it according to your instructions. It worked exactly as you said.
You led me on the right path
In the end, I simplified it. Because I have many different formats throughout the sheet, I've added my own format to the cells that contain the date
"'" mm.dd.yyy
This solved that I didn't have to edit the macro.

 

[Oddball2020]

Thank you for your response. I did it according to your instructions. It worked exactly as you said.
You led me on the right path
In the end, I simplified it. Because I have many different formats throughout the sheet, I've added my own format to the cells that contain the date
"'" mm.dd.yyy
This solved that I didn't have to edit the macro.

Great news, all the best!