compare current date to string date formatted "mmm-yy" using vba

0
votes

I have several sheets in my workbook named a variety of names such as "user", "52", and "Oct-13". I need to be able to compare the current date to the sheets that are named after dates, such as "Oct-13". If the current date is anytime after the date the sheet is named for, such as Oct-13, then I need to have VBA delete the sheet. The only way I know to do this is to extact the month and year from the sheet name and current date and compare those. Is there an easier way? I have not been able to find an easier, more effecient way.

Thanks in advance.

2
excelvba
How can you tell that it's past Oct-13? Is the sheet name in the format of mmm-yy or mmm-dd? I would assume year, but if not then you would need to make an assumption for the year. Have you made an attempt to solve this? Can you post it? – Harrison

2 Answers

0
votes

This shouldget you started. I've attempted to take account for numeric worksheet names (which can but should not be interpreted as dates, by checking to see if the sheetname contains a "-". You may need to refine this a bit.

Sub convertDates()
Dim strDate As String
Dim myDate As Date
Dim testDate As Date

testDate = DateSerial(Year(Now), Month(Now), Day(Now))

On Error GoTo InvalidDate
myDate = DateSerial(Year(strDate), Month(strDate), Day(strDate))

'You could do:
 For w = Sheets.Count To w Step -1
    strDate = Sheets(w).Name
    If InStr(1, strDate, "-", vbBinaryCompare) >= 4 Then
        If myDate < testDate Then
            Application.DisplayAlerts = False
            Sheets(w).Delete
            Application.DisplayAlerts = True
        End If
    End If
NextSheet:

 Next

Exit Sub
InvalidDate:
Err.Clear
'this worksheet's name cannot be interpreted as a date, so ignore and
' resume next
Resume NextSheet

End Sub
0
votes
Sub convertDates()
Dim strDate As String   'Formatted name of current sheet in loop
Dim deleteDate As Date  'The first day sheet is eligible for deletion
Dim currentDate As Date 'The current date as of the last time the Sub was ran
Dim sheet As Worksheet  'Used to loop through the sheets of the workbook

currentDate = DateSerial(Year(Now), Month(Now), Day(Now))

On Error GoTo InvalidDate

For Each sheet In ThisWorkbook.Worksheets
    If InStr(1, sheet.Name, "-", vbBinaryCompare) >= 4 Then
        strDate = Format(DateAdd("m", 1, sheet.Name), "yyyy,mmm")
        deleteDate = DateSerial(Year(strDate), Month(strDate), Day(strDate))
        If deleteDate <= currentDate Then
            Application.DisplayAlerts = False
            sheet.Delete
            Application.DisplayAlerts = True
        End If
    End If
NextSheet:

Next

Exit Sub
InvalidDate:
Err.Clear
'this worksheet's name cannot be interpreted as a date, so ignore and
' resume next
Resume NextSheet

End Sub