Last 90 days line graph without table

Shruen

New Member
Joined
Feb 17, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Trying to create a line graph in an excel spreadsheet showing the last 90 days without using a table. Got one line graph which shows all data, but need one for the last 90 days. Is there a way to do this with offset or is there another method? Below is an example of some data.
1613614188096.png
 

Attachments

  • 1613614155830.png
    1613614155830.png
    37.2 KB · Views: 2

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
292
Office Version
  1. 365
Platform
  1. Windows
Try this in a standard module:
VBA Code:
Function MonthNum(monthName As String) As Long
    MonthNum = Month(DateValue("1" & "/" & monthName & "/" & "2000"))
End Function

Sub SetChartDataWithin90Days()

    Dim date90 As String, newestYear As Range, newestMonth As Range, newestDay As Range, i As Long, startingRow As Long, searchMonth As Range, _
    searchYear As Range, searchLoop As Long, oldestDate As String, oldestDateMonth As String, oldestDateDay As String
    Set newestYear = Range("A" & Rows.Count).End(xlUp)
    Set newestMonth = Range("B" & Rows.Count).End(xlUp)
    Set newestDay = Range("C" & Rows.Count).End(xlUp)
    'Debug.Print newestDay & "/" & MonthNum(newestMonth.Value) & "/" & newestYear
    date90 = DateAdd("d", -90, newestDay & "/" & MonthNum(newestMonth.Value) & "/" & newestYear) 'Get date 90 days before the newest
    'Debug.Print date90
    
    'Get the oldest date within 90 days preceding the newest date
    For i = newestDay.Row To 2 Step -1
    
        'If in second or later loop reset ranges
        If Not searchMonth Is Nothing Then
            Set searchMonth = Nothing
        End If
        If Not searchYear Is Nothing Then
            Set searchYear = Nothing
        End If
        
        'Get the oldest month within 90 days preceding the newest date
        searchLoop = i
        Do
            If Not IsEmpty(Cells(i, "B")) Then
                Set searchMonth = Cells(i, "B")
            Else 'If month can't be obtained keep searching above row
                Set searchMonth = Cells(searchLoop, "B")
                searchLoop = searchLoop - 1
            End If
        Loop While IsEmpty(searchMonth)
        
        'Get the oldest year within 90 days preceding the newest date
        searchLoop = i
        Do
            If Not IsEmpty(Cells(i, "A")) Then
                Set searchYear = Cells(i, "A")
            Else 'If year can't be obtained keep searching above row
                Set searchYear = Cells(searchLoop, "A")
                searchLoop = searchLoop - 1
            End If
        Loop While IsEmpty(searchYear)
        
        'Check if a date in a row is within 90 days preceding the newest date
        oldestDateMonth = MonthNum(searchMonth.Value)
        oldestDateDay = Cells(i, "C")
        If Len(oldestDateMonth) = 1 Then
            oldestDateMonth = "0" & oldestDateMonth
        End If
        If Len(oldestDateDay) = 1 Then
            oldestDateDay = "0" & oldestDateDay
        End If
        oldestDate = oldestDateMonth & "/" & oldestDateDay & "/" & Right(searchYear, 2)
        If date90 > oldestDate Then
            startingRow = Rows(i + 1).Row
            Exit For
        End If
        'Debug.Print MonthNum(searchMonth.Value) & "/" & Cells(i, "C") & "/" & Right(searchYear, 2)
    Next i
    
    'If impossible to trace back to the past 90days
    If startingRow = 0 Then
        startingRow = 2
    End If
    'Debug.Print startingRow
    
    'Set chart data
    ActiveSheet.ChartObjects(1).Chart.SetSourceData Source:=Range(Cells(startingRow, "E"), Cells(newestDay.Row, "F"))
    ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).XValues = Range(Cells(startingRow, "B"), Cells(newestDay.Row, "C"))
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,334
Messages
5,624,087
Members
416,010
Latest member
NJT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top