I'm stuck on Formatting a Date

[JeffLeites]

I've been formatting a few dates throughout hundreds of lines of code and it works. Now I'm down to the last few lines of my program, and I'm getting an overflow on a date I'm trying to reformat. To simplify things, A wrote a few lines of code that show what I'm trying to do. What am I doing wrong?

Image1.jpg

[PCI]

DateYYYYMMDD is a string and you want to format it as date ...!
Change it to a date

[JeffLeites]

That didn't help

Image3.jpg

[PCI]

See next code

Option Explicit

Sub MySub()
Dim DateYYYYMMMDD As Date
Dim DateMMDDYYYY  As String


DateYYYYMMMDD = DateSerial(2020, 3, 1)

DateMMDDYYYY = Format(DateYYYYMMMDD, "mmddyyyy")
MsgBox ("MMDDYYYY = " & DateMMDDYYYY)

End Sub

[JeffLeites]

That works as a test, but in my real application I have these lines that do work:

Dim CSVDate As String
Dim CSVDateYYYMMDD As String

CSVDateYYYMMDD = Format(CSVDate, "yyyymmdd")

I don't see the difference between that and what I'm trying to do now that gives me an overflow:

Dim PostingLogDate As String
Dim PostingLogDateMMDDYYYY As String As String

PostingLogDateMMDDYYYY = Format(PostingLogDate, "mmddyyyy")

[JeffLeites]

I gave up trying to figure it out and did it the long way:

Sub Button1_Click()
Dim DateYYYYMMDD As String
Dim DateMMDDYYYY As String
Dim DateYYYY As String
Dim DateMM As String
Dim DateDD As String

DateYYYYMMDD = "20200227"
DateYYYY = Left(DateYYYYMMDD, 4)
DateMM = Mid(DateYYYYMMDD, 5, 2)
DateDD = Right(DateYYYYMMDD, 2)
DateMMDDYYYY = DateMM & DateDD & DateYYYY

MsgBox ("MMDDYYYY = " & DateMMDDYYYY)

End Sub

I don't know why format would work going from mmddyyyy but not yyyymmdd