Looping back through dates in the past

[PipBoy808]

Hello,

I'm writing a function that will open the most recent file from within the last 14 days, based on today's date and a date that is present at the start of each filename. For example: "140101 File.xlsx" would be from January 1st 2014 or "131225.xlsx" the 25th December 2013 (yymmdd).

If today's date is 140204, how would I incorporate a loop that moves back in time through previous dates?

I tried simply subtracting 1 from the date:

Date = 140204

For i = 1 to 14
backdate = (Right(Date, 2) - i)
If Len(backdate) = 1 Then
backdate = "0" & backdate
Else
End If

Date="[named cell date reference]" & backdate

but that runs into a problem if you hit 0 (e.g. 140203, 140202, 140201, 1402??, 1402??). I somehow need the loop to be aware that it's dealing with a date and move from 140201 to 140131 as it loops back, i.e. from 1st February to 31st January.

Any help would be much appreciated.

 

[Luke M]

This should give you some enlightenment. Basically, store your value as a date, and then use the Format method to do the conversion.

Sub PrintDates()
'Example for how to subtract value from date
'and get a yymmdd format
Dim myDate As Date

'Initial value
myDate = Date

For i = 1 To 100
    Cells(i, "A") = Format(myDate - i, "yymmdd")
Next i
End Sub