Date format issue?

[jgscanlon]

I have some code that defines date values based on a holiday calendar I've created. This code has been working great in another subroutine, but it's suddenly been failing when getting to the "FileDate" variable. In debug mode, I can see that "ThisDate" has a setting, but the reformatting of "ThisDate" into "FileDate" is failing, and I can't see why.

I've defined "FileDate" as a String, but still not working... Any thoughts?

Function FileExists(FileName As String) As Boolean
     FileExists = (Dir(FileName) > "")
End Function

Sub DefineDates()
Dim ThisDate As Date
Dim FileDate As String
Dim ThisFile As String
Dim ThisDirectory as String
Dim OutputFile As String
Dim Holidays(1 To 45) As Date

'Define Holidays
    Holidays(1) = "1/1/2015"   'New Years
    Holidays(2) = "1/19/2015"  'MLK Day
    Holidays(3) = "2/16/2015"  'Presidents' Day
    Holidays(4) = "4/3/2015"   'Good Friday
    Holidays(5) = "5/25/2015"  'Memorial Day
    Holidays(6) = "7/3/2015"   'Independence Day
    Holidays(7) = "9/7/2015"   'Labor Day
    Holidays(8) = "11/26/2015" 'Thanksgiving
    Holidays(9) = "12/25/2015" 'Christmas
    Holidays(10) = "1/1/2016"    'New Years
    Holidays(11) = "1/18/2016"   'MLK Day
    Holidays(12) = "2/15/2016"   'Presidents' Day
    Holidays(13) = "3/25/2016"   'Good Friday
    Holidays(14) = "5/30/2016"   'Memorial Day
    Holidays(15) = "7/4/2016"    'Independence Day
    Holidays(16) = "9/5/2016"    'Labor Day
    Holidays(17) = "11/24/2016"  'Thanksgiving
    Holidays(18) = "12/26/2016"  'Christmas
    Holidays(19) = "1/2/2017"    'New Years
    Holidays(20) = "1/16/2017"   'MLK Day
    Holidays(21) = "2/20/2017"   'Presidents' Day
    Holidays(22) = "4/14/2017"   'Good Friday
    Holidays(23) = "5/29/2017"   'Memorial Day
    Holidays(24) = "7/4/2017"    'Independence Day
    Holidays(25) = "9/4/2017"    'Labor Day
    Holidays(26) = "11/23/2017"  'Thanksgiving
    Holidays(27) = "12/25/2017"  'Christmas
    Holidays(28) = "1/1/2018"    'New Years
    Holidays(29) = "1/15/2018"   'MLK Day
    Holidays(30) = "2/19/2018"   'Presidents' Day
    Holidays(31) = "3/30/2018"   'Good Friday
    Holidays(32) = "5/28/2018"   'Memorial Day
    Holidays(33) = "7/4/2018"    'Independence Day
    Holidays(34) = "9/3/2018"    'Labor Day
    Holidays(35) = "11/22/2018"  'Thanksgiving
    Holidays(36) = "12/25/2018"  'Christmas
    Holidays(37) = "1/1/2019"    'New Years
    Holidays(38) = "1/21/2019"   'MLK Day
    Holidays(39) = "2/18/2019"   'Presidents' Day
    Holidays(40) = "4/19/2019"   'Good Friday
    Holidays(41) = "5/27/2019"   'Memorial Day
    Holidays(42) = "7/4/2019"    'Independence Day
    Holidays(43) = "9/2/2019"    'Labor Day
    Holidays(44) = "11/28/2019"  'Thanksgiving
    Holidays(45) = "12/25/2019"  'Christmas

    Counter = 0
    ThisDirectory = C:\Users\june\desktop\
    ThisDate = WorksheetFunction.WorkDay(Date, -1, Holidays)
    OutputFile = "_output.xlsx"

    Do Until FileExists(ThisFile) = True Or Counter = 180
        ThisDate = ThisDate - 1
        FileDate = Format(ThisDate, "yyyy-mmm-dd")
        ThisFile = ThisDirectory & FileDate & OutputFile
        Counter = Counter + 1
    Loop

Workbooks.Open FileName:=ThisFile

End Sub

 

[mole999]

Did you try
Dim FileDate As String
Dim FileDate As Date

 

[Norie]

How is the code failing?

 

[jgscanlon]

I did, but it returns the date as a 5-digit number, rather than in the format I need (yyyy-mmm-dd). It is my understanding that when formatting a date, you must define it as a String rather than a Date.

 

[jgscanlon]

How is the code failing?

The field is totally empty. On the watch screen, it reads, FileDate = "".

 

[Norie]

Does that happen under a particular set of circumstances?

 

[jgscanlon]

Does that happen under a particular set of circumstances?

No, it's happening each time I run it.

What I don't understand is why it's failing when I'm trying to reformat an existing date (defined earlier in the sub).

 

[mole999]

if the date is current then it will be in the 42000 range, so maybe the number is right ?

 

[jgscanlon]

I figured it out - for whatever reason, this version of Excel doesn't like my FileExists function.

Using the code below accomplishes the same thing (though I've seen it fail in other versions/installations of Excel).

Dim filesys As Scripting.FileSystemObject
Set filesys = CreateObject("Scripting.FileSystemObject")

...
   Do Until filesys.FileExists(ThisFile) = True Or Counter = 180         ThisDate = ThisDate - 1
        FileDate = Format(ThisDate, "yyyy-mmm-dd")
        ThisFile = ThisDirectory & FileDate & OutputFile
        Counter = Counter + 1
    Loop

Workbooks.Open FileName:=ThisFile