Date format text conversion

[Dan]

I am trying to convert the current date in to a four digit date ex:
Jan 3 = 0103
Jan 4 = 0104 etc

What I get instead is 103 or 104 etc. If i go to another cell and use
=text(cell,"mmdd") I get the desired result or format the cell for text.
Unfortunately those are not options. Perhaps it's my approach. Here is what I
am using in my attempts:

Dim sdte As Date
Dim dv As String
sdte = Date
Range("D2").Value = Format(sdte, "mmdd")
Range("e2").Value = sdte

dv = DateValue(sdte)
Range("f3").Value = dv
Range("e4").Value = Format(dv, "mmdd")
Range("E6").Value = CDate(dv)

I have also tried Now()

I have also tried "mmmdd" and "0000" in the formats these give me results of
a different date because of the datevalue / system date (39450) any help is
greatly appreciated. Thanks

 

[Niek Otten]

Use

[D2].NumberFormat = "@"
[D2]=format(date,"mmyy")

Otherwise it is like typing 0103 in a cell: it is still interpreted as a number, unless you formatted it as text before.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am trying to convert the current date in to a four digit date ex:
| Jan 3 = 0103
| Jan 4 = 0104 etc
|
| What I get instead is 103 or 104 etc. If i go to another cell and use
| =text(cell,"mmdd") I get the desired result or format the cell for text.
| Unfortunately those are not options. Perhaps it's my approach. Here is what I
| am using in my attempts:
|
| Dim sdte As Date
| Dim dv As String
| sdte = Date
| Range("D2").Value = Format(sdte, "mmdd")
| Range("e2").Value = sdte
|
| dv = DateValue(sdte)
| Range("f3").Value = dv
| Range("e4").Value = Format(dv, "mmdd")
| Range("E6").Value = CDate(dv)
|
| I have also tried Now()
|
| I have also tried "mmmdd" and "0000" in the formats these give me results of
| a different date because of the datevalue / system date (39450) any help is
| greatly appreciated. Thanks