Week to date ( Sunday to Saturday)

[Grandebucko]

Hi,

I need to create a dynamic bar chart that shows data from Sunday to Saturday for current week. If column A contains dates, column B contains values to be displayed in bar chart. I want the bar chart to always start from the most recent Sunday and chart each day of the week until Saturday, then start again for the current week.

Is this possible?
Thanks

 

[DanteAmor]

Hi @Grandebucko, welcome to the forum!

Assuming that the headings are in row 1 and the data begins in row 2.
note: Just one detail, it is important that the cell format for dates is dd/mm/yyyy


Run this macro:

Sub [COLOR=#0000ff]Macro2[/COLOR]()
  Dim d1 As Date, d2 As Date, f As Range, r1 As Long, r2 As Long
  ActiveSheet.ChartObjects("Chart1").Activate
  d1 = Date - WorksheetFunction.Weekday(Date) + 1
  d2 = d1 + 6
  Set f = Range("A:A").Find(d1, , xlValues, xlWhole)
  If Not f Is Nothing Then r1 = f.Row Else r1 = 2
  Set f = Range("A:A").Find(d2, , xlValues, xlWhole, , xlPrevious)
  If Not f Is Nothing Then r2 = f.Row Else r2 = Range("A" & Rows.Count).End(xlUp).Row
  ActiveChart.SetSourceData Source:=Range("A1:B1,A" & r1 & ":B" & r2)
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Macro2) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[DanteAmor]

No matter the date format, use the following:

Sub Macro2()
  Dim d1 As Date, d2 As Date, f As Range, r1 As Long, r2 As Long
  ActiveSheet.ChartObjects("Chart1").Activate
  d1 = Date - WorksheetFunction.Weekday(Date) + 1
  d2 = d1 + 6
  Set f = Range("A:A").Find(d1, , [COLOR=#0000ff]xlFormulas[/COLOR], xlWhole)
  If Not f Is Nothing Then r1 = f.Row Else r1 = 2
  Set f = Range("A:A").Find(d2, , xlFormulas, xlWhole, , xlPrevious)
  If Not f Is Nothing Then r2 = f.Row Else r2 = Range("A" & Rows.Count).End(xlUp).Row
  ActiveChart.SetSourceData Source:=Range("A1:B1,A" & r1 & ":B" & r2)
End Sub