Extract date format from sheet name

[Vincent88]

Hi Guys,
My sheet named as 2021(07) and I use below cell function code to extract its year, month and date to a cell C1.

=DATE(LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),4),MID(CELL("filename",A1),FIND("(",CELL("filename",A1))+1,FIND(")",CELL("filename",A1))-FIND("(",CELL("filename",A1))-1),1)

If my sheetname date format changed to 202107, what is the VBA code to extract the date form to 1-Jul to cell D1 and rest of the days (till 31st to cell AH1 and 30-Jun in C1).
Thanks.

 

[steve the fish]

You could do this:

Sub EnterDates()

Dim i As Long, myDate As Date

With ActiveSheet
    If IsNumeric(.Name) And Len(.Name) = 6 Then
        .Range("C1:AH1").ClearContents
        myDate = DateSerial(Left(.Name, 4), Right(.Name, 2), 1) - 1
        For i = 0 To Day(WorksheetFunction.EoMonth(myDate, 1))
            .Cells(1, 3 + i) = myDate + i
        Next
    End If
End With
    
End Sub

 

[Peter_SSs]

.. or you could try this without looping

Sub Fill_Dates()
  With ActiveSheet
    .Range("C1").Value = DateSerial(Left(.Name, 4), Right(.Name, 2), 0)
    .Range("C1").DataSeries xlRows, xlChronological, xlDay, 1, DateSerial(Left(.Name, 4), Right(.Name, 2) + 1, 0)
  End With
End Sub

 

[Vincent88]

Hi Steve the fish & Peter SSs,
I realized the codes also extract the last day of previous month. Is it possible to extract last five days of previous month if the start day of the month is in Column H. (Color C to G is last five days of previous month).
Can I put this as worksheet module instead of standard module ?

 

[Peter_SSs]

To me your request is unclear. Could we have some sample data and expected results with XL2BB and further explanation in relation to that sample data?

 

[Vincent88]

Hi, I mean based on the sheet name 202107, extract the days from June 25 to July 31 from cell C1 to AL1. Your code extract June 30 to July 31.

 

[Alex Blakenburg]

Hi, I mean based on the sheet name 202107, extract the days from June 25 to July 31 from cell C1 to AL1. Your code extract June 30 to July 31.

If you want the last 5 days then you want from Jun 26 not 25.
@Peter_SSs ' s code is easily modifiable.

The first line gives the starting value in C1. To include 5 days in June (which is from 26 Jun) just subtract 4 from the days position in the DateSerial function in that starting value line.

Sub Fill_Dates()
  With ActiveSheet
    .Range("C1").Value = DateSerial(Left(.Name, 4), Right(.Name, 2), -4)
    .Range("C1").DataSeries xlRows, xlChronological, xlDay, 1, DateSerial(Left(.Name, 4), Right(.Name, 2) + 1, 0)
  End With
End Sub

 

[Vincent88]

Hi Alex, Thanks, it works. Is it necessary to put this in worksheet module instead of standard module since each sheet should have this ?

 

[Peter_SSs]

Is it necessary to put this in worksheet module instead of standard module since each sheet should have this ?

If you want to apply this to all worksheets (that are named with 6 digits) then you could just run the code below from a standard module, rather than repeating it in every such worksheet.

Sub Fill_Dates_v2()
  Dim ws As Worksheet
  
  For Each ws In Worksheets
    If ws.Name Like "######" Then
      With ws
        .Range("C1").Value = DateSerial(Left(.Name, 4), Right(.Name, 2), -4)
        .Range("C1").DataSeries xlRows, xlChronological, xlDay, 1, DateSerial(Left(.Name, 4), Right(.Name, 2) + 1, 0)
      End With
    End If
  Next ws
End Sub

 

[Vincent88]

Thanks both of you. ?