Change dates to weekly

[CHML]

Hi. How could I get an average amount per week? Let's say from Sat - Fri. Also, is there a way I could format dates from the current view to a weekly cycle? e.g. A3:A8 should all be the last Friday of that week to show all these dates belong to this billing cycle.

So it would look like this

Once I get it like this I could quickly get it done via a Pivot Table.

Thank you in advance.

 

[Anonymous1378]

Once you get it into a pivot table, change your field to average instead of sum?

Book1
	A	B	C
3	07/28/2023	$68.00	07/28/2023
4	07/27/2023	$68.00	07/28/2023
5	07/26/2023	$68.00	07/28/2023
6	07/25/2023	$68.00	07/28/2023
7	07/24/2023	$68.00	07/28/2023
8	07/23/2023	$68.00	07/28/2023
Sheet1

Cell Formulas
Range	Formula
C3:C8	C3	=CEILING.MATH(A3,7)-1

 

[kevin9999]

As far as the first part of your requirement goes (converting all dates to Fridays) - please try the following on a copy of your data. Just change the sheet name to suit.

Option Explicit
Sub Fridays()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")       '<~~ *** Change to actual sheet name ***
    With ws.Range("A3", ws.Cells(Rows.Count, "A").End(xlUp))
        .Value2 = Evaluate(.Address(, , , 1) & "+7-WEEKDAY(" & .Address(, , , 1) & "+1)")
    End With
End Sub

 

[CHML]

Thank you!