Finding Date(s) in a Column in Order to Label Report as Either Single Date or Date Range

[FrenchCelt]

Hello,

I have a report that would either cover a single day, a week, or a 4-week range. The date(s) are displayed in Column D of my report and what I want to do is to label the cell in F2 the single date or have the date range in F2 and G2 depending on what is found in Column D in a macro.

So for example, if it only finds one date in Column D, I would like that date to be copied into F2.

If it finds a range of seven days, I would like cell L1 to say Weekly Productivity and the earliest date to be copied into F2 and the latest date into G2.

If it finds a range of 28 days, I would like cell L1 to say 4-Week Rolling Report and the earliest date to be copied into F2 and the latest date into G2.

Any help would be appreciated.

 

[BigBeachBananas]

Hello,

I have a report that would either cover a single day, a week, or a 4-week range. The date(s) are displayed in Column D of my report and what I want to do is to label the cell in F2 the single date or have the date range in F2 and G2 depending on what is found in Column D in a macro.

So for example, if it only finds one date in Column D, I would like that date to be copied into F2.

If it finds a range of seven days, I would like cell L1 to say Weekly Productivity and the earliest date to be copied into F2 and the latest date into G2.

If it finds a range of 28 days, I would like cell L1 to say 4-Week Rolling Report and the earliest date to be copied into F2 and the latest date into G2.

Any help would be appreciated.

What if it's more than 28 days?

 

[FrenchCelt]

I never run a report for a date range longer than 28 days. I only run single day, 7 day, and 28 day reports.

 

[BigBeachBananas]

Are the dates in the column D sorted in order?

 

[FrenchCelt]

No, the earliest date is not necessarily going to be at the top and the latest date is not necessarily going to be at the bottom.

 

[BigBeachBananas]

How about this? Assuming your date data starts in row 2.

Sub ReportHeader()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rng As Range
    Dim cell As Range
    Dim c As Long
    Dim minDate As Date
    Dim maxDate As Date
   
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace sheet name as needed
    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
   
    Set rng = ws.Range("D2:D" & lastRow)
   
    If IsDate(ws.Range("D2").Value) Then
        minDate = ws.Range("D2").Value
        maxDate = ws.Range("D2").Value
    End If
   
    For Each cell In rng
        If IsDate(cell.Value) Then
            c = c + 1
            If cell.Value < minDate Then
                minDate = cell.Value
            End If
            If cell.Value > maxDate Then
                maxDate = cell.Value
            End If
        End If
    Next cell
   
    If c = 1 Then
        ws.Range("F2").Value = minDate
    ElseIf c < 8 Then
        ws.Range("L1").Value = "Weekly Productivity"
        ws.Range("F2").Value = minDate
        ws.Range("G2").Value = maxDate
    ElseIf c < 29 Then
        ws.Range("L1").Value = "4-Week Rolling Report"
        ws.Range("F2").Value = minDate
        ws.Range("G2").Value = maxDate
    End If
End Sub

 

[FrenchCelt]

I'm getting a subscript out of range error for this line:

Set ws = ThisWorkbook.Sheets("Export")

I changed the sheet's name to what appears in my report.

 

[BigBeachBananas]

I'm getting a subscript out of range error for this line:

Set ws = ThisWorkbook.Sheets("Export")

I changed the sheet's name to what appears in my report.

It should be the sheet that the data date is on. Make sure the spelling is exact and no spaces.

 

[FrenchCelt]

It's still not working. I even renamed the sheet to something else so I could control what name to use in your code, but same error.

 

[BigBeachBananas]

Your workbook isn't able to find that sheet name. Ensure that you have saved your workbook after changing the sheet name to "Export" or the correct name.