How to convert date to text in Excel

Somehow I had to create a database in Excel by day for the whole year. For further control, I had to translate the date into text. 

You could just set the start date and then stretch it, but the fact is that when the cell had the “Date” type, some glitches appeared. I had to convert it to text type. And here again the problem is – if the cell format of the “Date” type is translated into the “Text” type, this is the thing that comes out:

I don’t know about everyone else, but it was easier for me to throw in a small macro.

Double-clicking on the modules tab in the window that opens, throws the following:

Macro parsing. In it I use three variables:

• newdate – type “Date” for iterating over the date,
• dateWrite – type “Text” to convert the date to text,
• a is the number to change cells.

a = 2 – initial cell number. Then we assign the initial date “January 1, 2021” to the newdate variable – #1/1/2021#, run the loop until the year of the date changes to 2022. 
In the loop, the dateWrite variable is assigned the date, we write the dateWrite value to the cell , then we increase the cell number for one, and the date for one day. And the cycle starts again.

Full macro text:

SubNewyear()
    Dim new date As Date
    Dim dateWrite As String
    a = 2
    Date = #1/1/2021#
    newdate = Date
    dateWrite = year(newdate)
    Sheets(dateWrite).Select
    Do
        dateWrite = newdate
        Range("A" & a).Value = dateWrite
        a = a + 1
        newdate = newdate + 1
    Loop Until year(newdate) = 2022
    
end sub

I read an article about the today() function , remembered this method and decided to share it. This is my first post, so don’t judge too harshly. Maybe someone will come in handy.